Show duplicates from across multiple ranges in one cell with a single nested formula?

[VikingLink]

Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data				TEXTSPLIT(A2;0;", ")	TEXTSPLIT(A3;0;", ")	TEXTSPLIT(A4;0;", ")		VSTACK(E2#;F2#;G2#)		UNIQUE(FILTER(I2#;(COUNTIF(I2#;I2#)>1)))		TEXTJOIN(", ";TRUE;K2#)
2	Jerry, Layne, Mike, Sean				Jerry	Eddie	Layne		Jerry		Layne		Layne, Mike
3	Eddie, Stone, Mike, Jeff, Dave				Layne	Stone	Mike		Layne		Mike
4	Layne, Mike, John, Barrett				Mike	Mike	John		Mike
5					Sean	Jeff	Barrett		Sean
6	Desired result					Dave			Eddie
7	Layne, Mike	Only duplicates							Stone
8									Mike
9									Jeff
10									Dave
11									Layne
12									Mike
13									John
14									Barrett
15
16
17
18
19					TEXTJOIN(", ";TRUE;K2#)				VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", "))		UNIQUE(FILTER(VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", "));(COUNTIF(I2#;I2#)>1)))
20					UNIQUE(FILTER(I2#;(COUNTIF(I2#;I2#)>1)))				Jerry		Layne
21					VSTACK(E2#;F2#;G2#)				Layne		Mike
22					TEXTSPLIT(A4;0;", ")				Mike
23					TEXTSPLIT(A3;0;", ")				Sean
24					TEXTSPLIT(A2;0;", ")				Eddie		UNIQUE(FILTER(VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", "));(COUNTIF(I2#;VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", ")))>1)))
25									Stone		Layne
26									Mike		Mike
27									Jeff
28									Dave
29									Layne		UNIQUE(FILTER(VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", "));(COUNTIF(VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", "));VSTACK(TEXTSPLIT(A2;0;", ");TEXTSPLIT(A3;0;", ");TEXTSPLIT(A4;0;", ")))>1)))
30									Mike		PROBLEM! Replacing the range in the COUNTIF part of the formula gives me a prompt asking if I'm not trying to write a formula.
31									John
32									Barrett
33
34
35
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=TEXTSPLIT(A2,0,", ")
F2:F6	F2	=TEXTSPLIT(A3,0,", ")
G2:G5	G2	=TEXTSPLIT(A4,0,", ")
I2:I14	I2	=VSTACK(E2#,F2#,G2#)
K2:K3	K2	=UNIQUE(FILTER(I2#,(COUNTIF(I2#,I2#)>1)))
M2	M2	=TEXTJOIN(", ",TRUE,K2#)
I20:I32	I20	=VSTACK(TEXTSPLIT(A2,0,", "),TEXTSPLIT(A3,0,", "),TEXTSPLIT(A4,0,", "))
K20:K21	K20	=UNIQUE(FILTER(VSTACK(TEXTSPLIT(A2,0,", "),TEXTSPLIT(A3,0,", "),TEXTSPLIT(A4,0,", ")),(COUNTIF(I2#,I2#)>1)))
K25:K26	K25	=UNIQUE(FILTER(VSTACK(TEXTSPLIT(A2,0,", "),TEXTSPLIT(A3,0,", "),TEXTSPLIT(A4,0,", ")),(COUNTIF(I2#,VSTACK(TEXTSPLIT(A2,0,", "),TEXTSPLIT(A3,0,", "),TEXTSPLIT(A4,0,", ")))>1)))
Dynamic array formulas.

I've been trying to work out a way to use one long nested formula to show duplicates from among multiple ranges in a single cell, but I keep getting stuck once I try to combine my multiple formulas.
In the example sheet below you can see what I'm trying to do, and how I combine my formulas step by step.

The data is in cells A2, A3, and A4. The desired result is as you can see in A7. I want it to show the duplicates from the three cells, in just one cell. In E1, F1 and G1 you can see my first step: splitting the data in the arrays to have each name in a single cell. I stack these in I1 so I have just one single array. I then filter that array in K1 to show only the duplicates. That array is then joined in M1. The issue is that I need to be able to do this for a few hundred cells, so I can't spread this across multiple columns, and need to be able to have just 1 formula combining all of these steps.

Below the blacked out cells you can see me combining these step by step, with the results shown below the separate functions in row 1.

Stacking the split up names works just fine. But once I get to the filtering, things go wrong. For some reason I can't get this to work. Once I replace the array of the COUNTIF function with the VSTACKED cells, I get a prompt asking me if I'm not trying to type a formula. Changing the array of I2# at the criteria of the COUNTIF works, and it also works for the array of the FILTER.

Can someone help me with this? And possibly explain why it's not working, so I can learn from this?

Thanks in advance.

 

[hagia_sofia]

Hello, as it seems that you are using Office 365 if the total number of characters within the data set is < 32767 you could e.g. go with:

=LET(
a,A2:A4,
b,TOCOL(TEXTSPLIT(TEXTJOIN(", ",,a),", ")),
TEXTJOIN(", ",,UNIQUE(FILTER(b,COUNTIFS(a,"*"&b&"*")>1))))

If it is higher, then e.g. with:

=LET(
a,A2:A4,
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,TRANSPOSE(TEXTSPLIT(y,", "))))),1),
TEXTJOIN(", ",,UNIQUE(FILTER(b,COUNTIFS(a,"*"&b&"*")>1))))

 

[Peter_SSs]

@hagia_sofia
I don't think either of those formulas are very robust. Test with my sample data below.

@VikingLink
As @hagia_sofia has already noted, it appears you have MS 365 so perhaps you should consider updating your forum profile to reflect that?
I am also wondering if it is possible with your data to have the same name more than once in the same cell? eg "Sam, Jen, Sam"

For the moment I am assuming total number of characters within the data set is < 32767 (should allow up to around 3 or 4 thousand names altogether)

24 10 16.xlsm
	A
1	Data
2	Jerry, Layne, Mike, Sean, Johnathan
3	Eddie, Stone, Mike, Jeff, Dave, Jo
4	Layne, Mike, John, Barrett, Keddie
5
6	Desired result
7	Layne, Mike
Dupes

Cell Formulas
Range	Formula
A7	A7	=LET(n,TEXTSPLIT(TEXTJOIN(", ",1,A2:A4),,", "),x,REDUCE("",UNIQUE(n),LAMBDA(s,d,s&IF(ROWS(FILTER(n,n=d))>1,", "&d,""))),MID(x,3,LEN(x)))

 

[hagia_sofia]

Indeed, @Peter_SSs, many thanks for pointing that out, my bad. @VikingLink please ignore post #2 as it would not bring the desired results, go with Peter_SSs' solution instead.

 

[VikingLink]

Hi

Thanks for the suggestions. I'll give it a try later today.
As for my version of Excel, I'm indeed using 365 for enterprises, with beta access (don't remember which version it of the beta it is though).

 

[VikingLink]

As for your question, no, the same name will not appear in the same cell. It can only appear in multiple cell.

 

[Peter_SSs]

Thanks for the suggestions. I'll give it a try later today.

Cheers. Thanks for the follow-up.
Let us know (with examples) if any problems.

.. and thank you for updating your profile.

 

[VikingLink]

Peter_SSs, I've just tried your solution, and it works. I had to add a textjoin to it, because my data in the actual file isn't in adjacent rows and colums, but it does work.

Could you tell me why the way I was trying it, didn't work? The problem in my way of going about it, faltered with the COUNTIF function's array. But I don't understand why it wouldn't accept the array as a formule which combined multiple cells.

 

[Peter_SSs]

Peter_SSs, I've just tried your solution, and it works.

Good news. Thanks for the confirmation.

I had to add a textjoin to it, because my data in the actual file isn't in adjacent rows and colums,

There may (just possibly) be other/better ways if we knew the layout. If interested to see, give us some sample data in the appropriate layout with XL2BB and tell us your final formula.

Could you tell me why the way I was trying it, didn't work? The problem in my way of going about it, faltered with the COUNTIF function's array. But I don't understand why it wouldn't accept the array as a formule which combined multiple cells.

COUNTIF will not accept an array as its first argument, it has to use a range. Annoying, but that's just how it is I'm afraid.
From COUNTIF help:

 

[VikingLink]

Good news. Thanks for the confirmation.



There may (just possibly) be other/better ways if we knew the layout. If interested to see, give us some sample data in the appropriate layout with XL2BB and tell us your final formula.



COUNTIF will not accept an array as its first argument, it has to use a range. Annoying, but that's just how it is I'm afraid.
From COUNTIF help:

View attachment 118118

In the actual file the data is always on the same row, but separated by a few columns with other data.

Thanks for the explanation (and again for the help).