Concatenate unique text values in Excel 2016 (based on criteria in range)

[art_TD]

Hi! Could you please help with such issue?
I am trying to concatenate unique (exact) text values within a range (Name) based on a criteria in another range (Code). No VBA please, just formula(s).

Code	Name	Concat
12345​	Roller	Roller	Roller; idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Roller		idler; sprocket;
12345​	Idler	Idler	Idler; sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Idler		sprocket;
12345​	Sprocket	Sprocket	Sprocket;
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
54321​	Roller	Roller	Roller; ???; ???
54321​	Idler
54321​	Sprocket

 

[Vogateer]

Like this?
Cell D2 when first two columns start at A1:

=TEXTJOIN("; ",,UNIQUE(CHOOSECOLS(FILTER($A$2:$B$31, $A$2:$A$31=A2),2)))

 

[art_TD]

Like this?
Cell D2 when first two columns start at A1:

=TEXTJOIN("; ",,UNIQUE(CHOOSECOLS(FILTER($A$2:$B$31, $A$2:$A$31=A2),2)))

I don't have such function on Excel 2016...

 

[Vogateer]

I don't have such function on Excel 2016...

Sorry about that. I'll try to find an alternative, but someone else probably knows better how to make this work.

 

[Sufiyan97]

Hi & Welcome to Mr. Excel.

Try

Book1
	A	B	C	D
1	Code	Name	Concat	Formula
2	12345	Roller	Roller	Roller; Idler; Sprocket;
3	12345	Roller		Idler; Sprocket;
4	12345	Roller		Idler; Sprocket;
5	12345	Roller		Idler; Sprocket;
6	12345	Roller		Idler; Sprocket;
7	12345	Roller		Idler; Sprocket;
8	12345	Roller		Idler; Sprocket;
9	12345	Idler	Idler	Idler; Sprocket;
10	12345	Idler		Sprocket;
11	12345	Idler		Sprocket;
12	12345	Idler		Sprocket;
13	12345	Idler		Sprocket;
14	12345	Idler		Sprocket;
15	12345	Idler		Sprocket;
16	12345	Idler		Sprocket;
17	12345	Idler		Sprocket;
18	12345	Idler		Sprocket;
19	12345	Sprocket	Sprocket	Sprocket;
20	12345	Sprocket
21	12345	Sprocket
22	12345	Sprocket
23	12345	Sprocket
24	12345	Sprocket
25	12345	Sprocket
26	12345	Sprocket
27	12345	Sprocket
28	12345	Sprocket
29	54321	Roller	Roller	Roller; Idler; Sprocket
30	54321	Idler	Idler	Idler; Sprocket
31	54321	Sprocket	Sprocket	Sprocket
32
Sheet1

Cell Formulas
Range	Formula
C2:C31	C2	=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,B2,"")
D2:D31	D2	=IF(A3="",C2,IF(A2<>A3,"",IF(C2="",D3,C2&"; "&D3)))

 

[art_TD]

Hi Sufiyan97,

It is what I needed. Great and thanks for your help!

Just curious why the 1st group has a semicolon at the end of concatenated texts while the 2nd doesn't. (I actually has a solution with a helper column to remove ";")

 

[art_TD]

And one more question - if the order of codes is random (codes are mixed within this range), would it be possible to keep looking for unique values and retrieve them if available?

Code	Name
12345​	Roller	Roller	Roller; Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Idler	Idler	Idler; Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Sprocket	Sprocket	Sprocket;
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
54321​	Roller	Roller	Roller; Idler;
54321​	Idler	Idler	Idler;
54321​	Sprocket	Sprocket
12345​	Track Shoe	Track Shoe	Track Shoe

 

[Sufiyan97]

Hi Sufiyan97,

It is what I needed. Great and thanks for your help!

Just curious why the 1st group has a semicolon at the end of concatenated texts while the 2nd doesn't. (I actually has a solution with a helper column to remove ";")

I have modified formula and added a column E for without semicolon

Book1
	A	B	C	D	E	F
1	Code	Name	Concat	Formula with semicolon	Formula without semicolon
2	12345	Roller	Roller	Roller; Idler; Sprocket;	Roller; Idler; Sprocket
3	12345	Roller		Idler; Sprocket;	Idler; Sprocket
4	12345	Roller		Idler; Sprocket;	Idler; Sprocket
5	12345	Roller		Idler; Sprocket;	Idler; Sprocket
6	12345	Roller		Idler; Sprocket;	Idler; Sprocket
7	12345	Roller		Idler; Sprocket;	Idler; Sprocket
8	12345	Roller		Idler; Sprocket;	Idler; Sprocket
9	12345	Idler	Idler	Idler; Sprocket;	Idler; Sprocket
10	12345	Idler		Sprocket;	Sprocket
11	12345	Idler		Sprocket;	Sprocket
12	12345	Idler		Sprocket;	Sprocket
13	12345	Idler		Sprocket;	Sprocket
14	12345	Idler		Sprocket;	Sprocket
15	12345	Idler		Sprocket;	Sprocket
16	12345	Idler		Sprocket;	Sprocket
17	12345	Idler		Sprocket;	Sprocket
18	12345	Idler		Sprocket;	Sprocket
19	12345	Sprocket	Sprocket	Sprocket;	Sprocket
20	12345	Sprocket
21	12345	Sprocket
22	12345	Sprocket
23	12345	Sprocket
24	12345	Sprocket
25	12345	Sprocket
26	12345	Sprocket
27	12345	Sprocket
28	12345	Sprocket
29	54321	Roller	Roller	Roller; Idler; Sprocket;	Roller; Idler; Sprocket
30	54321	Idler	Idler	Idler; Sprocket;	Idler; Sprocket
31	54321	Sprocket	Sprocket	Sprocket;	Sprocket
32
Sheet1

Cell Formulas
Range	Formula
C2:C31	C2	=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,B2,"")
D2:D31	D2	=IF(A3="",C2&";",IF(A2<>A3,"",IF(C2="",D3,C2&"; "&D3)))
E2:E31	E2	=IF(A3="",C2,IF(A2<>A3,"",IF(C2="",E3,C2&IF(E3="","","; ")&E3)))

 

[Sufiyan97]

And one more question - if the order of codes is random (codes are mixed within this range), would it be possible to keep looking for unique values and retrieve them if available?

Code	Name
12345​	Roller	Roller	Roller; Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Roller		Idler; Sprocket;
12345​	Idler	Idler	Idler; Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Idler		Sprocket;
12345​	Sprocket	Sprocket	Sprocket;
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
12345​	Sprocket
54321​	Roller	Roller	Roller; Idler;
54321​	Idler	Idler	Idler;
54321​	Sprocket	Sprocket
12345​	Track Shoe	Track Shoe	Track Shoe

Can you post some manually typed expected results?

 

[art_TD]

It should be like this after all.