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
GB12345678910,LU12345678910,US12345678910

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]ISIN[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]LU12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]US12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910,US12345678910[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]US12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910,US12345678910[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]US12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910,US12345678910[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]US12345678910[/TD]
[TD="bgcolor: #FFFFFF"]GB12345678910,LU12345678910,US12345678910[/TD]

</tbody>

Sheet2​

Worksheet Formulas Cell <tbody> [TH="align: left"]Formula[/TH] [TH="bgcolor: #DAE7F5"]B2[/TH] [TD="align: left"]=IF( COUNTIF($A$2:$A2,A2)>1,B1,IF(B1="",A2,B1&","&A2) )

<tbody>

[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF( AND(A2<>"",A3=""),B2,"" )[/TD]

</tbody>

[/TD]

</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...

[Table="*******, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]RESULT[/td][td]ISIN[/td][td][/td][/tr]
[tr][td]

2​

[/td][td][/td][td]GB12345678910[/td][td]GB12345678910[/td][/tr]
[tr][td]

3​

[/td][td][/td][td]GB12345678910[/td][td] GB12345678910[/td][/tr]
[tr][td]

4​

[/td][td][/td][td]GB12345678910[/td][td] GB12345678910[/td][/tr]
[tr][td]

5​

[/td][td][/td][td]GB12345678910[/td][td] GB12345678910[/td][/tr]
[tr][td]

6​

[/td][td][/td][td]GB12345678910[/td][td] GB12345678910[/td][/tr]
[tr][td]

7​

[/td][td][/td][td]GB12345678910[/td][td] GB12345678910[/td][/tr]
[tr][td]

8​

[/td][td][/td][td]LU12345678910[/td][td]LU12345678910 GB12345678910[/td][/tr]
[tr][td]

9​

[/td][td][/td][td]LU12345678910[/td][td] LU12345678910 GB12345678910[/td][/tr]
[tr][td]

10​

[/td][td][/td][td]LU12345678910[/td][td] LU12345678910 GB12345678910[/td][/tr]
[tr][td]

11​

[/td][td][/td][td]LU12345678910[/td][td] LU12345678910 GB12345678910[/td][/tr]
[tr][td]

12​

[/td][td][/td][td]LU12345678910[/td][td] LU12345678910 GB12345678910[/td][/tr]
[tr][td]

13​

[/td][td][/td][td]LU12345678910[/td][td] LU12345678910 GB12345678910[/td][/tr]
[tr][td]

14​

[/td][td][/td][td]US12345678910[/td][td]US12345678910 LU12345678910 GB12345678910[/td][/tr]
[tr][td]

15​

[/td][td][/td][td]US12345678910[/td][td] US12345678910 LU12345678910 GB12345678910[/td][/tr]
[tr][td]

16​

[/td][td][/td][td]US12345678910[/td][td] US12345678910 LU12345678910 GB12345678910[/td][/tr]
[tr][td]

17​

[/td][td]US12345678910, LU12345678910, GB12345678910[/td][td]US12345678910[/td][td] US12345678910 LU12345678910 GB12345678910[/td][/tr]
[/table]

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
Result	ISIN
	GB12345678910	GB12345678910
	GB12345678910	GB12345678910
	GB12345678910	GB12345678910
	GB12345678910	GB12345678910
	GB12345678910	GB12345678910
	GB12345678910	GB12345678910
	LU12345678910	GB12345678910,LU12345678910
	LU12345678910	GB12345678910,LU12345678910
	LU12345678910	GB12345678910,LU12345678910
	LU12345678910	GB12345678910,LU12345678910
	LU12345678910	GB12345678910,LU12345678910
	LU12345678910	GB12345678910,LU12345678910
	US12345678910	GB12345678910,LU12345678910,US12345678910
	US12345678910	GB12345678910,LU12345678910,US12345678910
	US12345678910	GB12345678910,LU12345678910,US12345678910
GB12345678910,LU12345678910,US12345678910	US12345678910	GB12345678910,LU12345678910,US12345678910

<colgroup><col style="******* 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</tbody>

Sheet9​

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=IF(AND(B2<>"",B3=""),C2,"")[/TD]
[/TR]
[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(COUNTIF($B$2:$B2,B2)>1,C1,IF(C1="",B2,C1&","&B2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]