How to match columns in excel and delete rows if unmatched - (Feb/29/2012 )

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.

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?)

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.