How do I concatenate groups of data into rows in excel

Question

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.

Does this answer your question? [Grouping labels and concatenating their text values (like a pivot table)](https://superuser.com/questions/453041/grouping-labels-and-concatenating-their-text-values-like-a-pivot-table) — Destroy666, Apr 21 '23 at 13:06
@Destroy666 its the same questions I did not see it. However all the answers are dated. Should I edit this question to refer to the version? — user1605665, Apr 24 '23 at 00:11
Probably, yeah. Although old answers could easil still work, Microsoft products don't change too much. — Destroy666, Jun 14 '23 at 06:37

score 0 · Answer 1 · answered Apr 22 '23 at 16:13

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.

score 0 · Answer 2 · answered Apr 22 '23 at 22:09

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
                        )
                    )
                )
            )
        )
    )
)

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

How do I concatenate groups of data into rows in excel

2 Answers2