Are there duplicate in non-contiguous columns in same row disregarding blanks?

[jdorby]

Hi -- I'm trying to write a formula that looks at several different arrays of text values, that are located in non-contiguous columns, and say is there are duplicate names listed or not. I'm attaching a screenshot that shows an example.

Array 1 = {Lead1, Lead2}
Array 2 = {Manager1, Manager2}
Array 3 = {Associate1, Associate2}

The formula would have to disregard blank cell values as well. In the example, you can see that Lead1 and Associate1 both have "Lomez" as a value, so the result should say that there are duplicates

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Are there duplicate in non-contiguous columns in same row disregarding blanks?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jdorby]

Hi -- I'm trying to write a formula that looks at several different arrays of text values, that are located in non-contiguous columns, and say is there are duplicate names listed or not. I'm attaching a screenshot that shows an example.

Array 1 = {Lead1, Lead2}
Array 2 = {Manager1, Manager2}
Array 3 = {Associate1, Associate2}

The formula would have to disregard blank cell values as well. In the example, you can see that Lead1 and Associate1 both have "Lomez" as a value, so the result should say that there are duplicates

View attachment 109452

Cross posted here: Are there duplicate in non-contiguous columns in same row disregarding blanks?

 

[Cubist]

Maybe this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Lead1	Lead2			Manager1	Manager2			Associate1	Associate2			Duplicate
2		Lomez	Bob Cobb			Kramer				Lomez	Newman			Lomez
3
4
Sheet9

Cell Formulas
Range	Formula
N2	N2	=LET(data,TOCOL(HSTACK(B2:C2,F2:G2,J2:K2),3),br,BYROW(data,LAMBDA(r,ROWS(FILTER(data,r=data)))),UNIQUE(FILTER(data,br>1)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jdorby]

In your example, what is "data" referring to?

 

[Cubist]

In your example, what is "data" referring to?

The combined names from
Array 1 = {Lead1, Lead2}
Array 2 = {Manager1, Manager2}
Array 3 = {Associate1, Associate2}

 

[jdorby]

Ok sorry, I realized that was a silly question after I sent it.

So that actually worked wonderfully! However, it brought back a list of the duplicate names -- do you know how I'd alter it just to say "Yes" duplicates do exist or "No" duplicates do not exist and only return one cell value (Yes or No) instead of an array of values that are duplicated?

That was an insane calculation that I never have seen so thank you so much already for your help!

 

[Cubist]

Try

=LET(data,TOCOL(HSTACK(B2:C2,F2:G2,J2:K2),3),br,BYROW(data,LAMBDA(r,ROWS(FILTER(data,r=data)))),IF(SUM(--(br>1)),"Yes","No"))

 

[jdorby]

You're amazing. I thought I was good at Excel up until now. Thank you!