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

Question

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:

SKU	A	B	C
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

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.

Answer 1

Try:

---

• Formula used in cell E2

=TOROW(
    BYROW(
        UNIQUE(
            REPT(
                $B$1:$D$1,
                B2:D2 =
                    TOCOL(
                        B2:D2
                    )
            )
        ),
        LAMBDA(x,
            TEXTJOIN(", ", , x)
        )
    )
)

---

You can also accomplish your end goal by using Power Query 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

---

let
    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}})
in
    #"Grouped Rows"

---

---

• 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.

---