Integration of text columns

New Contributor

Hi to all,

I can use some help with somthing....

I have a list with the colums -name, phone, email activity.

Some, or more like, alot of the people appear in more than one row, with each row of information in a column of" activity" is different.

I want to combine the information into a table where each person's details appear in one row, so that in the "Activity" cell all the different activities will appear with a comma separating them.

Name | phone |email | activity

xxxx | xxxx |xxxx| activity1,activity2, ect

Any idea how to do that?

5 Replies

@Xtra13 

Use the concat formula with ", " in between each column.

ex. =concat(A1, ", ", B1, ", ", C1, ", ")

@Xtra13 

This can probably be done efficiently with PowerQuery, but here is a solution using formulas; it requires that you have Microsoft 365 or Office 2021.

@Xtra13 

With Power Query maybe like in the attached file.

@Hans Vogelaar 

The attached version of your workbook could demonstrate that it is always possible to make a topic more complicated, given the right tools!  My formula

= Table1Restructured

clearly hides more than it exposes, but in detail it could be

able1Restructured = LET(
    distinct,UNIQUE(Table1[[Name]:[Email]]),
    distinctName, TAKE(distinct,,1),
    activities, MAP(distinctName,
        LAMBDA(n,
            TEXTJOIN(", ",,FILTER(Table1[Activity],Table1[Name]=n))
        )
    ),
    HSTACK(distinct, activities)
)

Of course, each of the methods will get that much more complicated if one allows for different email addresses to appear against a single name.

Big thanx to everyone