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

by

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…

Or, in a single go, exploiting TOCOL():

=LET(x,UNIQUE(TOCOL(A:A,1)),HSTACK(x,MAP(x,LAMBDA(y,TEXTJOIN(",",,FILTER(B:B,A:A=y))))))

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.

More Related Contents:

  1. Excel UNIQUE Across Columns
  2. Calculation is wrong, Rounding wrong
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Error in finding last used cell in Excel with VBA
  6. Convert matrix to 3-column table (‘reverse pivot’, ‘unpivot’, ‘flatten’, ‘normalize’)
  7. Getting unique values in Excel by using formulas only
  8. Vlookup using 2 columns to reference another
  9. Ignore Duplicates and Create New List of Unique Values in Excel
  10. Excel Formula for Inventory
  11. Find last used cell in Excel VBA
  12. Two column lookup in table array using INDEX and MATCH
  13. Using UNIQUE with non-adjecent columns on different sheets
  14. PowerQuery: How can I concatenate grouped values?
  15. Concatenate column headers if value in rows below is non-blank
  16. If two cells match, return value from third
  17. What does — do in Excel formulas?
  18. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  19. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  20. Using the value in a cell as a cell reference in a formula?
  21. Countif With Multiple OR Criteria
  22. Excel – extracting top 5 values
  23. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  24. CountIf With Filtered Data
  25. How to count cells in a range with a value less than another cell in excel?
  26. Extract the last substring from a cell
  27. How to extract text within a string of text
  28. How to roundup a number to the closest ten?
  29. Convert an Array Formula’s Text Results into a Usable Format
  30. Can’t pass a range to a lambda using Let and Makearray functions

Leave a Comment