compare data in column A with column B to find duplicates
 

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.

compare data in column A with column B to find duplicates
 

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

On Feb 5, 3:32*pm, George wrote:
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.

compare data in column A with column B to find duplicates
 

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

Regards,
Fred

"Pete_UK" wrote in message
...
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

On Feb 5, 3:32 pm, George wrote:
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.

compare data in column A with column B to find duplicates
 

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.



"George" wrote:

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.

compare data in column A with column B to find duplicates
 

Thanks for pointing that out, Fred.

Pete

On Feb 5, 3:55*pm, "Fred Smith" wrote:
Typo. Should be:
=IF(ISNA(MATCH(A1,B:B,0)),"","duplicate")

Regards,
Fred

"Pete_UK" wrote in message

...
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

On Feb 5, 3:32 pm, George wrote:



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.- Hide quoted text -

- Show quoted text -

compare data in column A with column B to find duplicates
 

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" wrote:

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

Regards,
Fred

"Pete_UK" wrote in message
...
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

On Feb 5, 3:32 pm, George wrote:
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.

compare data in column A with column B to find duplicates
 

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.


"George" wrote in message
...
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" wrote:

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

Regards,
Fred

"Pete_UK" wrote in message
...
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

On Feb 5, 3:32 pm, George wrote:
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.

compare data in column A with column B to find duplicates
 

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

On Feb 6, 2:33*pm, George wrote:
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" wrote:
Typo. Should be:
=IF(ISNA(MATCH(A1,B:B,0)),"","duplicate")

Regards,
Fred

"Pete_UK" wrote in message
....
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

On Feb 5, 3:32 pm, George wrote:
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.- Hide quoted text -

- Show quoted text -

compare data in column A with column B to find duplicates
 

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

On Feb 6, 3:49*pm, Pete_UK wrote:
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

On Feb 6, 2:33*pm, George wrote:



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" wrote:
Typo. Should be:
=IF(ISNA(MATCH(A1,B:B,0)),"","duplicate")

Regards,
Fred

"Pete_UK" wrote in message
....
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

On Feb 5, 3:32 pm, George wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -