concatenate unique values, based on criteria without macros

A

atearth

New Member
Joined
Feb 25, 2015
Messages
39
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
  2. Web
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Please see the image

wZ3JP65.png

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.
 
Last edited:
Upvote 0
Without macros, you'd need the TEXTJOIN function available in Excel 365.

ABC
Certification NumberReference
ABC
CBA
ABC1
ABC2
1A
2A
2A
Certification Number
Reference123, 321, 111

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

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

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

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

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]321[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]321[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]111[/TD]

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

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

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

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

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

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

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

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

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

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]{=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,""),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
Thank you Eric W
user-online.png
I will give it a try on 365.

What can be done on 2016?

Would using text join be an option on 2016?
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
you can try with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Certification Number[/td][td=bgcolor:#5B9BD5]Reference[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]ABC[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]CBA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
123​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
321​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
321​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
111​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]ABC1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]ABC2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]1A[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4​
[/td][td]2A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]2A[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Certification Number[/td][td=bgcolor:#5B9BD5]
Input​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#DDEBF7]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Reference[/td][td=bgcolor:#70AD47]
List​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#E2EFDA]
123 321 111​
[/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Concatenate unique text values in Excel 2016 (based on criteria in range)
2
Replies
17
Views
499
art_TD
A
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
338
hagia_sofia
hagia_sofia
R
  • Question Question
VBA Inserting rows based on Column criteria
Replies
4
Views
106
myall_blues
myall_blues
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P
U
  • Question Question
Remove Duplicates from a cell/ Create a cell with unique values from a cell with duplicate Values.
Replies
1
Views
779
eiloken
E

Forum statistics

Threads
1,224,814
Messages
6,181,127
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top