Jump to content

  • Log in with Facebook Log in with Twitter Log in with Windows Live Log In with Google      Sign In   
  • Create Account

Submit your paper to J Biol Methods today!
Photo
- - - - -

How to match columns in excel and delete rows if unmatched

Excel office microsoft programming VBA

  • Please log in to reply
2 replies to this topic

#1 Neanderthal

Neanderthal

    Enthusiast

  • Active Members
  • PipPipPipPipPip
  • 42 posts
1
Neutral

Posted 29 February 2012 - 10:13 AM

Dear all,



People can tell me: "dude, post this in some microsoft excel forum, not here!". But let us see if there are some geeks here who can solve this problem.



I have a problem:



I have two excel (Office 2007) files. File 1 has around 15700 entries and File 2 had around 2000 entries. Each of these 2000 entries are also present in the 15700 entries in File 1 with variable number of occurrences. eg: XYZ in column A of File 2 might be present in column M of File 1 9 times (may be in row 1-6, 12, 4000, and 12432).


I need to compare each cells in Column M of File 1 with each cells in Column A of File 2. And if there is a match, it should retain the entire row in File 1 and delete the entire row (of File 1), if there is no match. For example:




File 1 File 2





Column M Column N Column A



40_ABC 123 40_ZAB

40_DEF 246 40_DEF

40_ABC 789 40_JKL

40_GHI A1B 40_MNO

40_JKL B1A 40_WXY

40_ABC SO 40_ABC

40_ABC ME

40_JKL TH

40_MNO IN

40_PQR GO

40_ZAB FT

40_WXY TH

40_STU IS

40_DEF WT

40_MNO A3Z



Should, after applying the formula, produce (the entries that has to be deleted are shown as underlined/italized entries):




File 1 File 2
Column M Column N Column A

40_ABC 123 40_ZAB
40_DEF 46 40_DEF
40_ABC 789 40_JKL
40_GHI A1B 40_MNO
40_JKL B1A 40_WXY
40_ABC SO
40_ABC ME
40_JKL TH
40_MNO IN
40_PQR GO
40_ZAB FT
40_WXY TH
40_STU IS
40_DEF WT
40_MNO A3Z



So, after apllying formula, columns A to N of the results File 1 will have only the information for entries 40_ZAB, 40_DEF, 40_JKL, 40_MNO and 40_WXY and rest of the rows will be deleted.



I tried the formula given here. But this will only show the matching entries. I have to delete the un-matched entries manually that takes a lot of time (I can not use filter, because of some technical issues)



I hope I will get some interesting suggestions soon!



Thanks a lot in advance!



K.

Attached Files


Edited by Neanderthal, 29 February 2012 - 10:35 AM.


#2 pito

pito

    Veteran

  • Global Moderators
  • PipPipPipPipPipPipPipPipPipPip
  • 1,321 posts
80
Excellent

Posted 07 April 2012 - 11:43 AM

Not sure you allready have an answer on how to do it, but cant you just use sort?

if you make it that whenever you find a match, excel copies this match in a new row, you can then sort the new row (while also selecting the other rows) so that only the rows that got copied are at the top.. and then its easy to cope and paste.


(but I am not 100% sure I understand what you want to do, as far as I get it, you have 2 lists and you want to compare them and if an item is present in both lists you want the information(row) of this list copied to paste it in another file, right?)

Edited by pito, 07 April 2012 - 11:44 AM.

If you don't know it, then ask it! Better to ask and look foolish to some than not ask and stay stupid.


#3 newborn

newborn

    Enthusiast

  • Active Members
  • PipPipPipPipPip
  • 71 posts
2
Neutral

Posted 11 April 2012 - 11:51 PM

As Pito pointed, you can sort and solve that.

Another way is highlight the duplicate, please search "Conditionally format unique or duplicate values" in the help of your excel. It will tell you how to do.





Also tagged with one or more of these keywords: Excel, office, microsoft, programming, VBA

Home - About - Terms of Service - Privacy - Contact Us

©1999-2013 Protocol Online, All rights reserved.