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

 

[art_TD]

I found out there could be some mistakes (on another longer list of codes and names) and got the formulas as follows.

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(NOT(A2=A3),"",IF(C2="",D3,C2&"; "&D3)))
E2:E31	E2	=IF(A3="",C2,IF(NOT(A2=A3),"",IF(C2="",E3,C2&IF(E3="","","; ")&E3)))

 

[Sufiyan97]

Can you post post #11 formulas along with data

 

[art_TD]

Hi! Sorry there should have been not only NOT function but as follows.

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(NOT(A2=A3),C2,IF(C2="",D3,C2&"; "&D3)))
E2:E31	E2	=IF(A3="",C2,IF(NOT(A2=A3),C2,IF(C2="",E3,C2&IF(E3="","","; ")&E3)))

I don't know how to put the sheet to my post. Hope you will understand it on the snapshot.

D - your formula, F - my adjusted variant.

 

[Sufiyan97]

Sorry, I am giving up here, I don't know how to get the results as expected in post #10.

Hopefully someone else can help you on this!

But out of curiosity, can you post the formula you adjusted in column F.

 

[art_TD]

=IF(A3="",C2&";",IF(NOT(A2=A3),C2,IF(C2="",D3,C2&"; "&D3)))

 

[art_TD]

Sorry.

=IF(A3="",C2&";",IF(NOT(A2=A3),C2,IF(C2="",F3,C2&"; "&F3)))

 

[Joe4]

art_TD,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution to your original question (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.

 

[art_TD]

Joe4,

Thanks for taking the time to do this! I will follow this rule in the future.