Count duplicates in non-adjacent cells

[CP11]

Hi,

How can I compare duplicates in a non-continuous range?

I would like to check if there are any duplicates values in a row, however only compare specified cells which are not next to each other.

I have been able to do this with conditional formatting, however need it as a cell value too.

I.e. in the below example the 2nd row doesn't count the value ABC124 in the 2nd or 6th column

Compare	Ignore	Compare	Ignore	Compare	Ignore	Compare	Ignore	Compare	Ignore	Duplicates in row?
ABC121	ABC124	ABC123	ABC126	ABC127	ABC128	ABC123	ABC130	ABC131	ABC132	Yes
ABC124	ABC124	ABC125	ABC126	ABC127	ABC124	ABC129	ABC130	ABC131	ABC132	No

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Compare	Ignore	Compare	Ignore	Compare	Ignore	Compare	Ignore	Compare	Ignore	Duplicates in row?
2	ABC121	ABC124	ABC123	ABC126	ABC127	ABC128	ABC123	ABC130	ABC131	ABC132	Yes
3	ABC124	ABC124	ABC125	ABC126	ABC127	ABC124	ABC129	ABC130	ABC131	ABC132	No
Main

Cell Formulas
Range	Formula
K2	K2	=LET(f,FILTER(A2:J2,$A$1:$J$1="Compare"),IF(COLUMNS(f)<>COLUMNS(UNIQUE(f,1)),"Yes","No"))
K3	K3	=LET(f,FILTER(A3:J3,$A$1:$J$1="Compare"),IF(COLUMNS(f)<>COLUMNS(UNIQUE(f)),"Yes","No"))

 

[CP11]

Hello,

This works perfectly, thank you. I have a slight amendment:

The columns to compare will always be the same (A, C, E, G, I) and won't have the 'Compare' / 'Ignore' headers above them.

Is it possible to filter by the specified columns, rather than filter columns with a 'Compare' header?

Thank you

 

[Fluff]

Ok, how about

=LET(f,FILTER(A2:J2,MOD(COLUMN($A$1:$J$1),2)=1),IF(COLUMNS(f)<>COLUMNS(UNIQUE(f,1)),"Yes","No"))

 

[CP11]

Thank you, that has worked perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

 

[CP11]

Hi. Sorry, one more thing!

How do I exclude blanks within the range from being counted as duplicates?

Thank you

 

[Fluff]

Try

=LET(f,FILTER(A2:J2,(MOD(COLUMN($A$1:$J$1),2)=1)*(A2:J2<>"")),IF(COLUMNS(f)<>COLUMNS(UNIQUE(f,1)),"Yes","No"))

 

[CP11]

Thank you, this works if there are non-blanks as well as blanks. But if the whole row is blank it throws a calc error:

 

[Fluff]

In that case try

=LET(f,FILTER(A2:J2,(MOD(COLUMN($A$1:$J$1),2)=1)*(A2:J2<>""),""),IF(COLUMNS(f)<>COLUMNS(UNIQUE(f,1)),"Yes","No"))