There are multiple ways of doing this: Excel or Power Query.
Excel
This is one way of doing it under Excel. You can try the following in cell E2 (Formula 1):
=LET(input, A2:C3, fAndF, SUBSTITUTE(INDEX(input,,2) & "," & INDEX(input,,3),
", ",","), names, INDEX(input,,1),
GETCOL, LAMBDA(x, TEXTSPLIT(TEXTJOIN(",",, x),,",",TRUE)),
mapresult, MAP(names, fAndF, LAMBDA(name,f,
LET(num, LEN(f) - LEN(SUBSTITUTE(f, ",","")) + 1, REPT(name&",",num)))),
HSTACK(GETCOL(mapresult), GETCOL(fAndF))
)
and here is the output:
Note: For large dataset the previous solution has a limitation, due to TEXTJOIN function. The maximum number of text you can concatenate is 253. In order to circumvent that you can use the following approach based on DROP/REDUCE/VSTACK functions to incrementally add new elements as we iterate (Formula 2):
=LET(input, A2:C3, fAndF, SUBSTITUTE(INDEX(input,,2) & "," &
INDEX(input,,3), ", ",","),names, INDEX(input,,1),
DROP(REDUCE("", names, LAMBDA(ac, name, VSTACK(ac, LET(
ff, TEXTSPLIT(XLOOKUP(name, names, fAndF), ","),
DROP(REDUCE("", ff, LAMBDA(acc, f, VSTACK(acc, HSTACK(name,f)))),1)
)))),1)
)
Power Query
With Power Query, there is no need to use M-code all the functionalities required are provided by the UI. Here is the corresponding M-code (Advanced Editor):
let
Source = Excel.CurrentWorkbook(){[Name="TB_INPUT"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Friends", type text},
{"Family", type text}}),
RemoveExtraSpaceAfterComma = Table.ReplaceValue(#"Changed Type",", ",",",Replacer.ReplaceText,
{"Friends", "Family"}),
#"Merge Friend and Family" = Table.CombineColumns(RemoveExtraSpaceAfterComma,{"Friends", "Family"},
Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"F&F"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merge Friend and Family",
{{"F&F", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta
[Serialized.Text = true] in type {itemType}}}), "F&F")
in
#"Split Column by Delimiter"
it will produce the following output:
Explanation
Excel
For Formula 1, we use use LET for easy reading and composition. The name fAndF represents the concatenation by a row of Friend and Family columns. We remove also the space of the comma (,) delimiter.
GETCOL is LAMBDA user-defined function. To convert an array of comma-delimited values on each row into a single column.
We use a MAP function to determine how many times we need to repeat the Name column value. The num:
LEN(f) - LEN(SUBSTITUTE(f, ",","")) + 1
is a trick to determine how many rows we need by counting the total number of commas (,).
The name mapresult generates the following output:
John,John,John,John,John,
Jane,Jane,Jane,Jane,
Now, all we need to do is to join both rows via TEXTJOIN, then split again by comma via TEXTSPLIT and this is what the user function GETCOL does.
Note: The fourth input argument in TEXTSPLIT (in GETCOL), is set to TRUE to avoid generating an empty row with the last delimiter at the end.
Finally, we combine the result via HSTACK.
For Formula 2, we take a different approach. We use a pattern for avoiding nested array error that TEXTSPLIT produces and other functions. Check the answer of this question: How to split texts from dynamic range? provided by: @JvdV. The main idea consists of combining DROP, REDUCE and VSTACK functions to produce a recursion to add rows on each new iteration. We use this idea twice. One for each names and the other one for fAndF items for a given name on first REDUCE call.
For each name on first REDUCE call we find via XLOOKUP the corresponding fAndF values, then we invoke TEXTSPLIT to get the corresponding array (ff) and for each element of ff (f) we invoke the second REDUCE call adding a 1x2 row with the name and the corresponding f value via HSTACK.
Under this pattern, we need to remove the first row, that contains the initialization value of the accumulator (ac, acc). The pattern is always the same:
DROP(REDUCE("", array, LAMBDA(acc, arr, VSTACK(acc, func))),1)
and func, is where we do the calculation to build the content of the row we want to add. Usually we need to create additional variables and we encapsulate the calculation inside a LET function call.
Power Query
Once we have the data defined in an Excel Table, then in Power Query view.
Remove extra space in the comma delimiter. We select both columns Friend and Family columns and then: Home->Replace Values searching for , and replacing it with ,.
Select again Friend and Family columns then Transform->Merge Column. We name the merged column: F&F and we indicate the Separator. Here is the output:
Now we need to split the column: Home -> Split Column -> By Delimiter. In the Advanced options select: Rows, indicating we want to do the spit by rows. Here are the configuration options:
As @Ron Rosenfeld pointed out in the comments section. Another way of doing it is as follows:
After removing extra space after the comma (#RemovedExtraSpaceAfterComma) Select Family and Friend columns, then right-click and select: Unpivot Columns. Select the resulting Value column and Home->Split Column->By Delimiter and in Advanced options select Rows. Finally, remove the Attribute column.
Here is the M-code:
let
Source = Excel.CurrentWorkbook(){[Name="TB_INPUT"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Friends", type text}, {"Family", type text}}),
RemoveExtraSpaceAfterComma = Table.ReplaceValue(#"Changed Type",", ",",",Replacer.ReplaceText,{"Friends", "Family"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(RemoveExtraSpaceAfterComma, {"Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns",
{{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta
[Serialized.Text = true] in type {itemType}}}), "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute"})
in
#"Removed Columns"
