Excel dynamically transpose every time an email address is found

Question

I have a column in excel containing a long list similar to the following:

alfa.zulu@test.com
9v46by8
9016767312
TX961779
1DM90F4
bravo.zulu@test.com
B935536
24086942
9486388284
UAUG350583
0P47MB2
asd65f4
813asdg
357yvjy
jxvn97
iopu634
charlie.zulu@test.com
1DM90F4
0P47MB2
delta.zulu@test.com
9016767312
asd65f4
357yvjy
iopu634
echo.zulu@test.com
9v46by8
TX961779
B935536

I need to transpose the list, BUT every time I have an email address, I need to jump on down to the next row and start all over, such as the following:

alfa.zulu@test.com  9v46by8 9016767312  TX961779    1DM90F4                     
bravo.zulu@test.com B935536 24086942    9486388284  UAUG350583  0P47MB2 asd65f4 813asdg 357yvjy 
charlie.zulu@test.com   1DM90F4 0P47MB2                             
delta.zulu@test.com 9016767312  asd65f4 357yvjy iopu634                     
echo.zulu@test.com  9v46by8 TX961779    B935536 

                    

Is there any way to achieve this without using vba? Thanks in advance!

Answer 1

With Power Query:

• Make the column data type = text

• Test if an entry is email -- using the @ but could be more sophisticated

• Add an Index column

• Add another column which contains a unique number each time there is an email in column 1

• Fill down with the unique numbers so each "group" will have the same number

• Group the rows on the unique numbers column

• Extract the data from each row into a delimited list

• Add some logic to enable variations in the numbers of potential columns, else power query will not adapt.

• Split the list of data into new columns based on the delimiter

• Along the way, we delete extraneous columns

---

• Paste the code below into the Power Query Editor
• Change the Table in Line 2 to reflect the real table name in your worksheet.
• Double click on the statements in the Applied Steps window to explore what is being done at each step
• A refresh is all that should be required if your data table changes.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "isEmail", each Text.Contains([Column1],"@")),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Grouper", each if [isEmail]  then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Grouper"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"isEmail", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Grouper"}, {{"Grouped", each _, type table [Column1=nullable text, Grouper=number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Value", each Table.Column([Grouped],"Column1")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Grouper", "Grouped"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns2", "numSplits", each List.Count([Value])),

        //Make column splitting dynamic for each refresh, in case maximum number of columns changes
        splits = List.Max(Table.Column(#"Added Custom3","numSplits")),
        newColList = List.Zip({List.Repeat({"Value"},splits),List.Generate(() => 1, each _ <= splits, each _ +1)}),
        #"Converted to Table" = Table.FromList(newColList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        newColNamesTbl = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From)), type text}),
        newColNamesList = Table.Column(newColNamesTbl,"Column1"),
        
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Value", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"numSplits"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), newColNamesList)
in
    #"Split Column by Delimiter"

Source Data

Results