2 replies to this topic

[Neanderthal]

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

•  Table.xls   19.5K   76 downloads

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

[pito]

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.

[newborn]

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.