Forum Discussion
Xtra13
Jun 14, 2022Copper Contributor
Integration of text columns
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 in...
HansVogelaar
Jun 14, 2022MVP
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.
PeterBartholomew1
Jun 14, 2022Silver Contributor
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
= Table1Restructuredclearly 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.
- Xtra13Jun 15, 2022Copper Contributor
Big thanx to everyone