Jun 14 2022 07:51 AM
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?
Jun 14 2022 08:04 AM
Use the concat formula with ", " in between each column.
ex. =concat(A1, ", ", B1, ", ", C1, ", ")
Jun 14 2022 08:07 AM
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.
Jun 14 2022 08:34 AM
With Power Query maybe like in the attached file.
Jun 14 2022 08:59 AM
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.
Jun 14 2022 11:26 PM - edited Jun 14 2022 11:29 PM
Big thanx to everyone