compare data in column A with column B to find duplicates

[George]

I was given a formula that did not work before and need to try this again.
I have two lists which I have put in one worksheet, column A is 797 rows
long and column B is 319 rows long. I need to isolate the duplicates in these
two columns so I can remove them and see what was not duplicated (all of the
items in column B will be duplicated in column A)
The lists are labels which consist of letters and numbers some have spaces
and other have signs like - , ( , ).
The lists are of finished items and un-finished items, the finished items
will be duplicated, so by removing those lables I will be left with the
un-finished items.
Any help with be appreciated.

 

[Pete_UK]

Put this in C1:

=IF(ISNA(MATCH(A1:B:B,0)),"","duplicate")

and copy down to row 797. It will tell you which of the entries in
column A are duplicated in column B.

Hope this helps.

Pete

 

[Fred Smith]

Typo. Should be:
=IF(ISNA(MATCH(A1,B:B,0)),"","duplicate")

Regards,
Fred

Put this in C1:

=IF(ISNA(MATCH(A1:B:B,0)),"","duplicate")

and copy down to row 797. It will tell you which of the entries in
column A are duplicated in column B.

Hope this helps.

Pete

 

[Sheeloo]

Looks like you just need to find out duplicates in Col A. If that is the case
then enter this in C1
=COUNTIF($A$1:A1,A1)

It will give you 1 against the value when it occurs for the first time, 2
for the second time and so on... You can filter on >1 to delete duplicates.

 

[Pete_UK]

Thanks for pointing that out, Fred.

Pete

Typo. Should be:
=IF(ISNA(MATCH(A1,B:B,0)),"","duplicate")

Regards,
Fred


Put this in C1:

=IF(ISNA(MATCH(A1:B:B,0)),"","duplicate")

and copy down to row 797. It will tell you which of the entries in
column A are duplicated in column B.

Hope this helps.

Pete



- Show quoted text -

 

[George]

I have put the corrected formula in C1 and copied it down to the end, now
what? In the C1 column it now says duplicate in evry row, do I need to hit
the 'enter' key or do something else? This may be a problem, the sheet is in
a CSV format, will that effect it?

 

[Fred Smith]

No, you don't have to hit the 'enter' key. No, CSV format should have no
effect on the results. The results you are getting mean that each entry in
column B exists in column A, which is what Pete thought you wanted to test
for.

If you are looking for duplicates *within* a column, change the formula to:
=IF(ISNA(MATCH(A1,A:A,0)),"","duplicate") for column A, and
=IF(ISNA(MATCH(B1,B:B,0)),"","duplicate") for column B.

Regards,
Fred.

 

[Pete_UK]

You said you needed "... to isolate the duplicates in these two
columns so I can remove them..."

You can apply autofilter to the column with the formula in, and choose
either "duplicate" from the filter pull down or "blanks", depending on
what you want to see. If you want to remove the duplicates then
highlight them with the filter applied and click on Edit | Delete Row,
then choose All from the filter pull-down to see what is left.

Hope this helps.

Pete

 

[Pete_UK]

Please Note:

deleting the duplicates will also delete the names in column B, so you
might want to make sure you do this on a copy of the data so that you
can retrieve it if you need to.

Pete