concatenate unique values, based on criteria without macros

[atearth]

Please see the imagine.


https://imgur.com/a/haW7m1x

I have a table of information.


you can seen cell C15 shows certification number is 2.


What formula should I use in cell C16 so it shows 123, 321, 111.


concatenate unique values only, based on criteria without macros?


How is it done?


Thank you in advance.

 

[atearth]

Please see the image

https://imgur.com/a/haW7m1x

I have a table of information.


you can seen cell C15 shows certification number is 2.


What formula should I use in cell C16 so it shows 123, 321, 111.


concatenate unique values only, based on criteria without macros?


How is it done?


Thank you in advance.

 

[Eric W]

Without macros, you'd need the TEXTJOIN function available in Excel 365.

	A	B	C
1
2		Certification Number	Reference
3		1	ABC
4		1	CBA
5		2	123
6		2	321
7		2	321
8		2	111
9		3	ABC1
10		3	ABC2
11		4	1A
12		4	2A
13		4	2A
14
15		Certification Number	2
16		Reference	123, 321, 111

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

Sheet1​

Array Formulas Cell Formula C16 {=TEXTJOIN(", ",1,IF(B3:B13=C15,IF(MATCH(B3:B13&"|"&C3:C13,B3:B13&"|"&C3:C13,0)=ROW(B3:B13)-ROW(B3)+1,C3:C13,""),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DanteAmor]

With an auxiliary column

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:109.31px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="background-color:#538ed5; color:#ffffff; ">CER NUM</td><td style="background-color:#538ed5; color:#ffffff; ">REF</td><td style="background-color:#538ed5; color:#ffffff; ">AUX</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">1</td><td >ABC</td><td >  123  321 111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">1</td><td >CA</td><td > 123  321 111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">123</td><td >123  321 111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">321</td><td > 321 111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">321</td><td >321 111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">111</td><td style="text-align:right; ">111      </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">3</td><td >ABC1</td><td >     </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">3</td><td >ABC2</td><td >    </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">1A</td><td >   </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">2A</td><td >  </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">2A</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="background-color:#538ed5; color:#ffffff; ">CER NUM</td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="background-color:#538ed5; color:#ffffff; ">REF</td><td >123, 321, 111</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D3</td><td >=IF(B3=$C$15,IF(COUNTIF(C3:$C$13,C3)=1,C3,""),"")&" "&D4</td></tr><tr><td >C16</td><td >=SUBSTITUTE(TRIM(D3)," ",", ")</td></tr></table></td></tr></table>

Formula in D3 copy down.

 

[atearth]

Thank you Eric W

I will give it a try on 365.

What can be done on 2016?

Would using text join be an option on 2016?

 

[Eric W]

No, TEXTJOIN is not available on 2016. It might be on 2019, and definitely is on 365. If you don't have one of those options, you'll have to use a helper column, like Dante's example. It's also worth noting that if you do use a helper (auxiliary) column, you can hide it so you don't have to see it.

 

[sandy666]

you can try with PowerQuery (Get&Transform)

Certification Number	Reference
1​	ABC
1​	CBA
2​	123​
2​	321​
2​	321​
2​	111​
3​	ABC1
3​	ABC2
4​	1A
4​	2A
4​	2A
Certification Number	Input​
	2​
Reference	List​
	123 321 111​

[SIZE=1]// Result
let
    Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Table.NestedJoin(Tbl2,{"Input"},Tbl1,{"Certification Number"},"Tbl1",JoinKind.LeftOuter),
    List = Table.AddColumn(Source, "List", each List.Distinct(Table.Column([Tbl1],"Reference"))),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    ROC = Table.SelectColumns(Extract,{"List"})
in
    ROC[/SIZE]