Integration of text columns

Question

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 | activityxxxx | xxxx |xxxx| activity1,activity2, ectAny idea how to do that?

ryankazacoff · Answer

Xtra13&nbsp;Use the concat formula with ", " in between each column.ex. =concat(A1, ", ", B1, ", ", C1, ", ")

hansvogelaar · Answer

Xtra13&nbsp;
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.

oliverscheurich · Answer

Xtra13&nbsp;With Power Query maybe like in the attached file.

peterbartholomew1 · Answer

HansVogelaar&nbsp;The attached version of your workbook could demonstrate that it is always possible to make a topic more complicated, given the right tools!&nbsp; My formula= Table1Restructuredclearly hides more than it exposes, but in detail it could beable1Restructured = 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.

xtra13 · Answer

Big thanx to everyone

Forum Discussion

Integration of text columns

5 Replies