How to return multiple comma-delimited column matches in Excel where columns equal same value?

I’ve been trying to figure out a way to do this using Lambda formulas, I’ve been testing different variations of textjoin/bycol/sumproduct formulas but can’t seem to get the result quite right.

I can accomplish this using a Filter formula, but when I’m using this formula for 15000+ rows, it takes a lot of processing power and trying to find a way to simplify it.

I’m trying to find a way to create a comma-delimited list of column names out of a group of 3 columns, where those columns equal each other (and will end up removing duplicates)

The source data looks like this:

SKU1 12345 12345 67890
SKU2 12345 12345 12345
SKU3 12345 67890 67890
SKU4 12345 67890 99999

My end goal is to make it look like this:

SKU Value Column
SKU1 12345 A,B
SKU1 67890 C
SKU2 12345 A,B,C
SKU3 12345 A
SKU3 67890 B,C
SKU4 12345 A
SKU4 67890 B
SKU4 99999 C

enter image description here

Creating a list of the columns would be very simple if I had an array of column number matches which I could use to index the column headers, however I can’t seem to figure out that part. Any tips would be greatly appreciated!

A little context, normally the columns "A" "B" and "C" are indicative of the area we will find a UPC for an item. A being on the Mother Carton, B being on the Inner Pack, and C being on the individual unit.

I’m trying to create a way to efficiently match UPCs, but currently I’m using a FILTER formula which uses a MATCH formula on all 3 separate columns, which is causing additional time to calculate because it’s very frequently reviewing the same UPC in multiple columns.

I’m hoping this will help to reduce the number of rows which that formula needs to search through, but still want to be able to identify all of the different areas we can find those UPCs.

>Solution :


enter image description here

• Formula used in cell E2

                B2:D2 =
            TEXTJOIN(", ", , x)

You can also accomplish your end goal by using Power Query here:

enter image description here

Follow the steps:

  • First convert the source range into a table and name it accordingly, for this example I have named it as Jointbl

  • Next, open a blank query from Data Tab –> Get & Transform Data –> Get Data –> From Other Sources –> Blank Query

  • The above lets the Power Query window opens, now from Home Tab –> Advanced Editor –> And paste the following M-Code by removing whatever you see, and press Done

    Source = Excel.CurrentWorkbook(){[Name="Jointbl"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"SKU"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"SKU", "Value"}, {{"Join", each Text.Combine([Attribute],","), type text}})
    #"Grouped Rows"

enter image description here

  • Lastly, to import it back to Excel –> Click on Close & Load or Close & Load To –> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result.

Leave a Reply