Compare 2 columns, delete cells with no match

[cmd]

I need to compare 2 columns on the same sheet, A & B, and then delete the cells in column A that do not match column B...Column A has 9153 rows and B has 1923 rows.

I had this matching the rows and returning the values that matched to another sheet in the workbook, but deleting the cells that don't match on the same sheet would be 10x more beneficial for me.

If someone could please help me out it would be much appreciated!

[JBeaucaire]

A1 match to B1, A2 match to B2, etc?

Or A1 match column B anywhere, A2 match to column B anywhere?

[cmd]

Sorry I didn't clarify...anywhere in the columns that they match

ex. B1 = A232, B2 = A649 and so on

[JBeaucaire]

So in effect, you will be searching the SMALL set of numbers for the ones that match in the BIG set, yes? Then you want an indication of which match?

A formula which would tell you...put this in C1 and copy down:

=ISNUMBER(MATCH(B1, A:A, 0))

[cmd]

Both of the columns are words and I would like to delete the ones in that subset that are not the same as in the large one

[JBeaucaire]

Try this macro version of the same idea on a COPY of your data, it will leave only the matches:

Please Login or Register  to view this content.

[JBeaucaire]

This version will delete values ONLY in the subset:

Please Login or Register  to view this content.

[Marcol]

Or possibly in C2

=INDIRECT("B"&MATCH(A2,B:B,0))

Filter on not #N/A and copy the result

Hope this helps

[EDIT]
Where did the last 3 posts appear from???...

[cmd]

This just deletes everything in the column and doesn't leave the matches...and I know that there are matches there

Please Login or Register  to view this content.

[MarvinP]

I think the first formula of:
=ISNUMBER(MATCH(B1, A:A, 0)) - in C1
should have been
=ISNUMBER(MATCH(A1,B:B,0)) and then copy down.

You can then sort all three columns by column C and delete the cells in A that match FALSE.

See attached sheet?

[cmd]

Does it matter how long the names are? Some of these names are long with an underscore in them as well...

ex: LOAD_HISTORY_END_TIMESTAMP

[JBeaucaire]

No it doesn't matter, but you remove ALL the guesswork we're doing here by posting a usable sample workbook.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[cmd]

Alright I took all of the values from both columns and posted them into a brand new workbook and it works just fine...I'm not sure why it wouldn't work in the current workbook?

Thank you guys for all of your help though, it it much appreciated !

[Marcol]

Maybe give this a try?

Please Login or Register  to view this content.

Hope this helps.

[danjc]

thank you for that Marcol it has helped