Find Duplicates Across Range of Cells Within a Row

[gshamilt]

I have a range of cells in a row I would like to check for text duplicates. See the image down below. I'm working with about 500 rows. I would love a formula to give me the result I have in cell G2. Sometimes a row may have no duplicates, 1 duplicate, 2 duplicates, 3 duplicates, etc.

If that is too difficult with a formula, simply marking which rows have a duplicate in cell G2 would also work. This would be my 2nd preferred option.

Conditional formatting where I highlight which cells are duplicates would be a 3rd preferred option that would also work.

Can someone help me with this? I'm struggling on it. Thanks!

 

[DanteAmor]

What version of Excel do you have?
A fourth option could be with a macro?

 

[DanteAmor]

This could be an alternative for your first option.

Book1
	A	B	C	D	E	F	G
1	Cat1	Cat2	Cat3	Cat4	Cat5	Cat6	Duplicate
2	Books	Cookbooks	Art books	Hardcover Books	Books	Art books	Books, Art books,
3	Books	Cookbooks	Art books	Cookbooks	Books	Art books	Books, Cookbooks, Art books,
sheet

Cell Formulas
Range	Formula
G2:G3	G2	=IF(COUNTIF(A2:F2,A2)>1,A2&", ","")&IF(COUNTIF(B2:F2,B2)>1,B2&", ","")&IF(COUNTIF(C2:F2,C2)>1,C2&", ","")&IF(COUNTIF(D2:F2,D2)>1,D2&", ","")&IF(COUNTIF(E2:F2,E2)>1,E2&", ","")

 

[gshamilt]

This could be an alternative for your first option.

Book1
	A	B	C	D	E	F	G
1	Cat1	Cat2	Cat3	Cat4	Cat5	Cat6	Duplicate
2	Books	Cookbooks	Art books	Hardcover Books	Books	Art books	Books, Art books,
3	Books	Cookbooks	Art books	Cookbooks	Books	Art books	Books, Cookbooks, Art books,
sheet

Cell Formulas
Range	Formula
G2:G3	G2	=IF(COUNTIF(A2:F2,A2)>1,A2&", ","")&IF(COUNTIF(B2:F2,B2)>1,B2&", ","")&IF(COUNTIF(C2:F2,C2)>1,C2&", ","")&IF(COUNTIF(D2:F2,D2)>1,D2&", ","")&IF(COUNTIF(E2:F2,E2)>1,E2&", ","")

That works beautifully! Thanks for your help. That's exactly what I needed.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.