Checking For Duplicates

[kparadise]

So I have a table of records. Each row is a record that contains 5 columns of items. I created these records manually, but now i need to identify any records (rows) that contain the same values across the 5 columns. See below: the first and third records are duplicates because they contain the same 5 value regardless of which column they fall in.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM[/TD]
[TD]ITEM[/TD]
[TD]ITEM[/TD]
[TD]ITEM[/TD]
[TD]ITEM[/TD]
[TD]DUP?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]apple[/TD]
[TD]orange[/TD]
[TD]grape[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bug [/TD]
[TD]apple[/TD]
[TD]orange[/TD]
[TD]cat[/TD]
[TD]dog[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange[/TD]
[TD]apple[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]grape[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]dog[/TD]
[TD]bird[/TD]
[TD]banana[/TD]
[TD]cat[/TD]
[TD]grape[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Probably a better solution, but try

=IF(AND(COUNTIFS($A:$E,A3)>1,COUNTIFS($A:$E,B3)>1,COUNTIFS($A:$E,C3)>1,COUNTIFS($A:$E,D3)>1,COUNTIFS($A:$E,E3)>1),"Y","N")

Gaz

 

[kparadise]

I see this, but it will not provide me with the results I want. I will only give me a "Y" for the second duplicate record, it will not give me a "Y" for both duplicate records.

 

[gaz_chops]

Seems to work ok for me!

[TABLE="width: 609"]
<tbody>[TR]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63"]ITEM[/TD]
[TD="class: xl63"]ITEM[/TD]
[TD="class: xl63"]ITEM[/TD]
[TD="class: xl63"]ITEM[/TD]
[TD="class: xl63"]ITEM[/TD]
[TD="class: xl63"]DUP?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]apple[/TD]
[TD="class: xl63"]orange[/TD]
[TD="class: xl63"]grape[/TD]
[TD="class: xl63"]dog[/TD]
[TD="class: xl63"]cat[/TD]
[TD="class: xl64"]Y[/TD]
[TD="class: xl65, align: center"]Y[/TD]
[/TR]
[TR]
[TD="class: xl63"]bug[/TD]
[TD="class: xl63"]apple[/TD]
[TD="class: xl63"]orange[/TD]
[TD="class: xl63"]cat[/TD]
[TD="class: xl63"]dog[/TD]
[TD="class: xl63"]N[/TD]
[TD="class: xl65, align: center"]N[/TD]
[/TR]
[TR]
[TD="class: xl63"]orange[/TD]
[TD="class: xl63"]apple[/TD]
[TD="class: xl63"]dog[/TD]
[TD="class: xl63"]cat[/TD]
[TD="class: xl63"]grape[/TD]
[TD="class: xl64"]Y[/TD]
[TD="class: xl65, align: center"]Y[/TD]
[/TR]
[TR]
[TD="class: xl63"]dog[/TD]
[TD="class: xl63"]bird[/TD]
[TD="class: xl63"]banana[/TD]
[TD="class: xl63"]cat[/TD]
[TD="class: xl63"]grape[/TD]
[TD="class: xl63"]N[/TD]
[TD="class: xl65, align: center"]N[/TD]
[/TR]
[TR]
[TD="class: xl63"]grape[/TD]
[TD="class: xl63"]cat[/TD]
[TD="class: xl63"]dog[/TD]
[TD="class: xl63"]apple[/TD]
[TD="class: xl63"]orange[/TD]
[TD][/TD]
[TD="class: xl65, align: center"]Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[kparadise]

Yes, you are correct. I miss read that formula, you started in row 3, ...my mistake. Thank you!

 

[gaz_chops]

You're welcome.

 

[Fluff]

That formula will not work in some cases, such as

Excel 2013/2016
	A	B	C	D	E	F
1	ITEM	ITEM	ITEM	ITEM	ITEM	DUP?
2	apple	orange	grape	dog	grape	Y
3	bug	apple	orange	cat	dog	Y
4	orange	apple	dog	cat	grape	Y
5	dog	bird	banana	cat	grape	N
6	apple	orange	bug	grape	horse	N
Jan2

Cell Formulas
Range	Formula
F2	=IF(AND(COUNTIFS($A:$E,A2)>1,COUNTIFS($A:$E,B2)>1,COUNTIFS($A:$E,C2)>1,COUNTIFS($A:$E,D2)>1,COUNTIFS($A:$E,E2)>1),"Y","N")

That said I'm not sure how to improve it.

 

[jimrward]

One way would be to create a helper column along with a custom function to take the 5 cells sort them alphabetically and join them together to make a unique string for your helper column and then use countif after that to highlight the duplicates

 

[gaz_chops]

Guess it depends on OP's interpretation of "Duplicates", it works if the word is duplicated no matter where the duplicate is.

 

[Fluff]

Agreed, but based on this

the first and third records are duplicates because they contain the [B]same 5 value[/B] regardless of which column they fall in.

I read it that all 5 values must be in the same row.
But I could be wrong.