Compare Columns and Identify Duplicates

Mattrixdesign · Mar 7th, 2002, 09:22 AM

I have two similar sets of data. I want excel to find and identify the duplicate rows. I have a feeling it could involve the MATCH or EXACT formulas. I have seen some examples but I can't get them to work.

I want to compare :-
Column A (A5:A100) and Colunm L (L5:L100), I need to excel to show which cells in column "L" already exisit in column "A". So Excel should either higlight the duplicate in a different colour or return a TRUE/FALSE or YES/NO result by each row of column "L".

EX/
Column A
RP08323
RP08950
RP09122
RP09122
RP06715
RP07936

Column L
RP08323(XL should ID this row)
RP08950(XL should ID this row)
RP09122(XL should ID this row)
RP07936(XL should ID this row)
RP07936(XL should ID this row)
RP02999 (this number is not in column A)

PLLEEASEE help!

[ This Message was edited by: Mattrixdesign on 2002-03-07 08:43 ]

[ This Message was edited by: Mattrixdesign on 2002-03-07 08:59 ]

Don C · Mar 7th, 2002, 09:46 AM

How many columns does it take to identify unique rows?

I've had worksheets where columns A and B are sufficient to identify the row. I'll create a new column which combines those columns (=A2&B2), then in yet another column I'll count the number of occurrances of each row. D2=COUNTIF(C$2:C$50,C2)

Obviously, column C in this example can include many more columns than just A and B.
Column D tells you how many occurrances of C there are, so any row with a value = 1 is unique. Any other value has a match.

Don

Don C · Mar 7th, 2002, 09:50 AM

OOps, my window did not show the whole message, so I missed the details. Countif is still a way to go.

=COUNTIF(A$5:A$100,L5)
drag down from L5 to L100. This column will give you a zero if the value in L5 does not occur in A5 to A100 and a value greater than zero if it does occur in column A.

Conditional formatting (zero = black, >0 = red) would make the row show up clearly.

Mattrixdesign · Mar 7th, 2002, 10:00 AM

Thanks Don!!!

I will check it out tomorrow as I am finishing work now. You didn't see all the message as I edited.

Cheers

Matt

Mattrixdesign · Mar 8th, 2002, 03:30 AM

Don

It works!!! thanks for your help, I really appreciate it.

All the best.

Matt

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 09:22 AM	#1
Mattrixdesign Board Regular Join Date: Mar 2002 Location: Warrington Posts: 195	I have two similar sets of data. I want excel to find and identify the duplicate rows. I have a feeling it could involve the MATCH or EXACT formulas. I have seen some examples but I can't get them to work. I want to compare :- Column A (A5:A100) and Colunm L (L5:L100), I need to excel to show which cells in column "L" already exisit in column "A". So Excel should either higlight the duplicate in a different colour or return a TRUE/FALSE or YES/NO result by each row of column "L". EX/ Column A RP08323 RP08950 RP09122 RP09122 RP06715 RP07936 Column L RP08323(XL should ID this row) RP08950(XL should ID this row) RP09122(XL should ID this row) RP07936(XL should ID this row) RP07936(XL should ID this row) RP02999 (this number is not in column A) PLLEEASEE help! [ This Message was edited by: Mattrixdesign on 2002-03-07 08:43 ] [ This Message was edited by: Mattrixdesign on 2002-03-07 08:59 ]

Mar 7th, 2002, 09:46 AM	#2
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	How many columns does it take to identify unique rows? I've had worksheets where columns A and B are sufficient to identify the row. I'll create a new column which combines those columns (=A2&B2), then in yet another column I'll count the number of occurrances of each row. D2=COUNTIF(C$2:C$50,C2) Obviously, column C in this example can include many more columns than just A and B. Column D tells you how many occurrances of C there are, so any row with a value = 1 is unique. Any other value has a match. Don

Mar 7th, 2002, 09:50 AM	#3
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	OOps, my window did not show the whole message, so I missed the details. Countif is still a way to go. =COUNTIF(A$5:A$100,L5) drag down from L5 to L100. This column will give you a zero if the value in L5 does not occur in A5 to A100 and a value greater than zero if it does occur in column A. Conditional formatting (zero = black, >0 = red) would make the row show up clearly.

Mar 7th, 2002, 10:00 AM	#4
Mattrixdesign Board Regular Join Date: Mar 2002 Location: Warrington Posts: 195	Thanks Don!!! I will check it out tomorrow as I am finishing work now. You didn't see all the message as I edited. Cheers Matt

Mar 8th, 2002, 03:30 AM	#5
Mattrixdesign Board Regular Join Date: Mar 2002 Location: Warrington Posts: 195	Don It works!!! thanks for your help, I really appreciate it. All the best. Matt