Forum Discussion

thepinkbird's avatar
thepinkbird
Copper Contributor
Mar 08, 2024

Multi-Row Data Consolidation Into Single Row, Multi-Column

I've been searching for a simple, easily repeatable solution to reformat a report that I generate on a biweekly basis.     In column A, we have the asset tags of a primary piece of equipment. ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 08, 2024

thepinkbird 

As another variant that could be Power Query which returns

with something like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveBlanks = Table.SelectRows(Source, each ([Tag] <> null)),
    GroupTags = Table.Group(
        RemoveBlanks,
        {"Tag"},
        {
              {"Data", each Text.Combine( [SN], ","), type text   }
            , {"Cols", each List.Count([SN]), type number}
        }),
    maxCols = List.Max(GroupTags[Cols]),
    RemoveCols = Table.RemoveColumns(GroupTags,{"Cols"}),
    names = List.Transform( {1..maxCols}, (q) => "S/N " & Text.From(q) ),

    SplitSN = Table.SplitColumn(
        RemoveCols,
        "Data",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), names)
in
    SplitSN

Resources