Excel: How to gather unique values in one column that are associated with duplicates in another column?

Question

With data such as this:

Column A     Column B
1               98
1               12
1               21 
1               31   
2               37
2               40
3               48 
4               34
4               88
4               74 
4               99
7               82
7               19
7               29
7               50 
7               95
7               85

where all values in Column B are unique, and Column A has multiple duplicates. How can I group the A values in one column, and display concatenated values of B in another, like this:

Column C   Column D
1             98,12,21,31
2             37,40
3             48
4             34,88,74,99
7             82,19,29,50,95,85

I've tried with a combination of concatenate and index and match, but it all just turns into a mess.

Any suggestions would be great?

Answer 1

Let me add two additional methods to the answer by @Harun24HR. Both options assume you don't have headers as per your sample data.

---

Option 1) : Dynamic Array Functions

When one has access to dynamic array functions you may use the following:

In C1:

=UNIQUE(A1:A17)

This UNIQUE function will spill an array of unique values from defined range into column C.

In D1:

=TEXTJOIN(",",TRUE,FILTER(B$1:B$17,A$1:A$17=C1))

Whereas FILTER will extract all values from column B where column A matches it is TEXTJOIN that will concatenate these values into your desired string.

Drag down...

---

Option 2) : PowerQuery

Would you want to experiment with PowerQuery/GetAndTransform then you don't need any formulas nor VBA for that matter. Follow these steps:

• Select A1:B17 and from the ribbon choose Data > From Table/Range under "Get & Transform Data"
• Choose to import data without headers. A new window will open.
• From the ribbon click Transform > Group By. Within that menu choose to group by Column1, choose a new column name, e.g.: "Grouped" and then choose All Rows from the Operation dropdown and click OK.
• You'll notice an extra column. Now on the ribbon click Add Column > Custom Column and enter the following formula: Table.Column([Grouped], "Column2"). This should add a third column that holds a list of values.
• Remove Grouped from the table. Then click on the icon to the right of the newly added column name, and you'll have two options. Choose Extract Values, then choose a comma as your delimiter.

There might be a translation-error in the M-code below, but this should be it:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Grouped", each _, type table [Column1=number, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped], "Column2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"})
in
    #"Removed Columns"

PowerQuery is available from Excel-2010 if I'm not mistaken so you wouldn't need access to advanced formulas like TEXTJOIN to perform this.

Answer 2

First, you need to extract unique value from Column A to Column C. You can do it by using Advance Filter method or you can use below formula.

=IFERROR(INDEX($A$2:$A$18,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$18),0,0),0)),"")

After extracting unique values you have to use TEXTJOIN() formula to aggregate values from Column B to Column D. You have TEXTJOIN() formula in your excel version then you can use it like below

=TEXTJOIN(", ",TRUE,IF($A$2:$A$18=C2,$B$2:$B$18,""))

Otherwise you have to user VBA custom function to write TextJoin() formula. For TEXTJOIN() custom function you can have look to this post. Post Link