2

I have a table where one column which is the value I want to keep and the other column I want to concatenate each repeated row value into a correcponding column (If someone has a better way to explain it please edit this)

The below sheet explains what I am trying to do. Is there a way to use formulas and possibly intermediate tables to do this.

I could use sum if to count the total for each value, but I wonder if there is a concat if.

excel data sheet

user1605665
  • 1,203

2 Answers2

1

If your intial table is Table3, and no constraints on Excel versions as indicated by your tags, then this formula entered into a single cell will produce your desired results:

=LET(
    v, SORT(
        UNIQUE(Table3[Value])
    ),
    g, Table3[Group],
    HSTACK(
        v,
        BYROW(
            v,
            LAMBDA(vals,
                TEXTJOIN(
                    ",",
                    ,
                    SORT(
                        FILTER(
                            g,
                            Table3[Value] =
                                vals
                        )
                    )
                )
            )
        )
    )
)

enter image description here

You'll need to look at HELP for the various functions to best understand how it works. But basically,

  • Create unique, sorted list of the values
  • Filter the table for each Value
    • Concatenate (with TextJoin) the Groups for each of those values
Ron Rosenfeld
  • 9,461
0

The answer suggested in the comment by Destroy666 includes an "old school" answer and a "modern" answer (using Power Query). Below is an inbetween answer, modern functions, but not Power Query (which might be overkill depending upon the size of your data and how often you need to address it.

The following formulas works with ranges for your sample data. Change the ranges to suit:

For the values to group by:

=SORT( UNIQUE( A1:A13 ))

For the groupings:

=TEXTJOIN( ", ", FALSE, SORT( FILTER( $B$1:$B$13, $A$1:$A$13 = $D2 )))

Copy, and paste down the groupings column column.

Jeorje
  • 1