Wondering how to transform table to remove redundant rows. See image for desired outcome.
CodePudding user response：
This should work:
=LET(rowID;A2:A7;uniID;UNIQUE(rowID);list;B2:B7;result;BYROW(uniID;LAMBDA(currentID;TEXTJOIN(", ";TRUE;FILTER(list;rowID=currentID))));CHOOSE({1\2};uniID;result))
CodePudding user response：
This is what I have tried and come up with the following multiple approaches,
The following formula is applicable to MS365
• Formula used in cell D2
=LET(u,UNIQUE(A2:A7),
contacts,BYROW(u,LAMBDA(a,TEXTJOIN(", ",,REPT(B2:B7,A2:A7=a)))),
CHOOSE({1,2},u,contacts))
Perhaps if you are not using MS365 then may be using Excel 2019 then use the below one,
• Formula used in cell D7 --> To get Unique Company Name,
=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(D$6:D6,$A$2:$A$7),0)),"")
• Formula used in cell E7 --> To get combined Contact Name in one single cell,
=TEXTJOIN(", ",,REPT(B2:B7,A2:A7=D7))
This can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac) -- Note for Excel 2010 - 2013 you need to download the 
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Company Name"}, {{"Contact Name", each Text.Combine([Contact Name], ", "), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Company Name", type text}, {"Contact Name", type text}})
in
#"Changed Type"
However, if you are in MS365 and when writing the below formula if you have enabled the Beta Channel from Office Insiders, then using LAMBDA() , BYROW(), TAKE(), DROP(), VSTACK() & HSTACK() use these newer formulas as well,
=LAMBDA(array,
LET(headers,TAKE(array,1),
ComName,DROP(array,1,-1),
ConName,DROP(array,1,1),
u,UNIQUE(ComName),
Cn,BYROW(u,LAMBDA(a,TEXTJOIN(", ",,FILTER(ConName,ComName=a)))),
VSTACK(headers,HSTACK(u,Cn))))(A1:B7)
Also note you can use a custom defined, reusable formula with a friendly name,
=LISTDATA(A1:B7)
Syntax =LISTDATA(array)
=LAMBDA(array,
LET(headers,TAKE(array,1),
ComName,DROP(array,1,-1),
ConName,DROP(array,1,1),
u,UNIQUE(ComName),
Cn,BYROW(u,LAMBDA(a,TEXTJOIN(", ",,FILTER(ConName,ComName=a)))),
VSTACK(headers,HSTACK(u,Cn))))
Which is a defined name --> copy the above formula shown above and paste in Refers to:
--> Formulas Tab --> Define Name --> Name --> LISTDATA --> Refers to:
