unique values from a column into one row, each item separated with a comma

[Tommeck37]

Hello,

Can anyone help me with this difficult task?



I am looking for a formula that would select only unique values that are located in next column. The values in next column can be duplicated.
What I need is a copy of each unique value from that column separated by comma in one cell. It can be the last row next to the filled in column.

I have prepared a file how it should look like at the end.

Please find a copy of excel spreadsheet in this location:

https://1drv.ms/x/s!AjQ2afcR5zV7kTLjyqiUzxb7Fgao

I need to emphasize that I need a formula (not vba code).

Many thanks in advance

Tommeck37

 

[AhoyNC]

If you have Excel 2016 you can use the TEXTJOIN function as shown in the attached link.

https://www.youtube.com/watch?v=QJ2O07EB80Q

 

[Tommeck37]

very interesting video. However, not useful for me as I have only 2013 MS Office

thank you

Tommeck37

 

[Tommeck37]

Additionally, we can add some columns to the left to process this task.

 

[AhoyNC]

You could use the formula in D17 below to list the unique items in columns and then concatenate them into one cell.
The formula in D17 would be copied across as needed (change ranges to match your data).

Excel Workbook
	A	B	C	D	E	F
1	*	ISIN	*	*	*	*
2	*	GB12345678910	*	*	*	*
3	*	GB12345678910	*	*	*	*
4	*	GB12345678910	*	*	*	*
5	*	GB12345678910	*	*	*	*
6	*	GB12345678910	*	*	*	*
7	*	GB12345678910	*	*	*	*
8	*	LU12345678910	*	*	*	*
9	*	LU12345678910	*	*	*	*
10	*	LU12345678910	*	*	*	*
11	*	LU12345678910	*	*	*	*
12	*	LU12345678910	*	*	*	*
13	*	LU12345678910	*	*	*	*
14	*	US12345678910	*	*	*	*
15	*	US12345678910	*	*	*	*
16	*	US12345678910	*	*	*	*
17	GB12345678910, LU12345678910, US12345678910	US12345678910	*	GB12345678910	LU12345678910	US12345678910
Sheet

 

[Tommeck37]

Thank you for your reply

However, the number of additional columns can be as many as possible but they need to be fixed (always the same) but the number of unique numbers is variable so one excludes the other.

Best Regards

Tommeck37

 

[Eric W]

You could try something like this:

	A	B	C
1	ISIN
2	GB12345678910	GB12345678910
3	GB12345678910	GB12345678910
4	GB12345678910	GB12345678910
5	GB12345678910	GB12345678910
6	GB12345678910	GB12345678910
7	GB12345678910	GB12345678910
8	LU12345678910	GB12345678910,LU12345678910
9	LU12345678910	GB12345678910,LU12345678910
10	LU12345678910	GB12345678910,LU12345678910
11	LU12345678910	GB12345678910,LU12345678910
12	LU12345678910	GB12345678910,LU12345678910
13	LU12345678910	GB12345678910,LU12345678910
14	US12345678910	GB12345678910,LU12345678910,US12345678910
15	US12345678910	GB12345678910,LU12345678910,US12345678910
16	US12345678910	GB12345678910,LU12345678910,US12345678910
17	US12345678910	GB12345678910,LU12345678910,US12345678910	GB12345678910,LU12345678910,US12345678910

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =IF(COUNTIF($A$2:$A2,A2)>1,B1,IF(B1="",A2,B1&","&A2)) C2 =IF(AND(A2<>"",A3=""),B2,"") <tbody> </tbody>

<tbody>
</tbody>

Put the formulas in B2:C2, then drag them down as far as needed. Ideally, you'd want to hide column B.

Let us know if this helps.

 

[Tommeck37]

Can you please the formula? I pasted it onto spreadsheet and got an error for B column. I did not yet try C

 

[Aladin Akyurek]

Also...

Row\Col	A​	B​	C​
1​	RESULT	ISIN
2​		GB12345678910	GB12345678910
3​		GB12345678910	GB12345678910
4​		GB12345678910	GB12345678910
5​		GB12345678910	GB12345678910
6​		GB12345678910	GB12345678910
7​		GB12345678910	GB12345678910
8​		LU12345678910	LU12345678910 GB12345678910
9​		LU12345678910	LU12345678910 GB12345678910
10​		LU12345678910	LU12345678910 GB12345678910
11​		LU12345678910	LU12345678910 GB12345678910
12​		LU12345678910	LU12345678910 GB12345678910
13​		LU12345678910	LU12345678910 GB12345678910
14​		US12345678910	US12345678910 LU12345678910 GB12345678910
15​		US12345678910	US12345678910 LU12345678910 GB12345678910
16​		US12345678910	US12345678910 LU12345678910 GB12345678910
17​	US12345678910, LU12345678910, GB12345678910	US12345678910	US12345678910 LU12345678910 GB12345678910

In C2 enter and copy down:

=IF(ISNA(MATCH($B2,$B$1:B1,0)),$B2," ")&IFERROR(T(VLOOKUP($B1,CHOOSE({1,2},$B1:$B$16,$C1:$C$16),2,0)),"")

In C17 enter:

=SUBSTITUTE(TRIM(LOOKUP(REPT("z",255),C:C))," ",", ")

which gives the result.

 

[Eric W]

What error are you getting? I adjusted the sample with the result column in A now. Put the formula in C2. Note that you have to have an empty cell above it in C1. Drag down from C2. Then put the formula in A2 and drag that down.

	A	B	C
1	Result	ISIN
2		GB12345678910	GB12345678910
3		GB12345678910	GB12345678910
4		GB12345678910	GB12345678910
5		GB12345678910	GB12345678910
6		GB12345678910	GB12345678910
7		GB12345678910	GB12345678910
8		LU12345678910	GB12345678910,LU12345678910
9		LU12345678910	GB12345678910,LU12345678910
10		LU12345678910	GB12345678910,LU12345678910
11		LU12345678910	GB12345678910,LU12345678910
12		LU12345678910	GB12345678910,LU12345678910
13		LU12345678910	GB12345678910,LU12345678910
14		US12345678910	GB12345678910,LU12345678910,US12345678910
15		US12345678910	GB12345678910,LU12345678910,US12345678910
16		US12345678910	GB12345678910,LU12345678910,US12345678910
17	GB12345678910,LU12345678910,US12345678910	US12345678910	GB12345678910,LU12345678910,US12345678910

<colgroup><col style="******* 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula A2 =IF(AND(B2<>"",B3=""),C2,"") C2 =IF(COUNTIF($B$2:$B2,B2)>1,C1,IF(C1="",B2,C1&","&B2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>