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

Copper Contributor

I've been searching for a simple, easily repeatable solution to reformat a report that I generate on a biweekly basis.

 

 exceldatatrouble.png

In column A, we have the asset tags of a primary piece of equipment. In column B, we have serial numbers of equipment associated with the assets in column A. My goal is to have all the data on a single row, so that second, third, etc. serial numbers are instead in columns C, D, etc. This is currently being done with tables of 5k rows, if that's relevant.

 

Any guidance is greatly appreciated!

 

 

5 Replies
=ifna(drop(reduce(0,unique(a2:a5000),lambda(x,y,vstack(x,torow(filter(b2:b5000,a2:a5000=y))))),1),"")

@thepinkbird 

Assuming you are on Excel 365, as variant if not to split S/N by columns but keep as text list

image.png

with formula

=LET(
 tagList, TOCOL( UNIQUE(tags), 3),
 relatedSN, MAP(tagList, LAMBDA(t, TEXTJOIN(", ",, FILTER( SN, tags=t) ) ) ),
 VSTACK(
   {"Tag","S/N"},
   HSTACK(tagList, relatedSN )
 )
)

@thepinkbird 

As another variant that could be Power Query which returns

image.png

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

@thepinkbird Another dynamic array formula, which may seem longer and more complex, but was designed for efficiency:

 

=LET(
    data, A2:B5001,
    id, INDEX(data,, 1),
    keyId, SORT(UNIQUE(id)),
    recordCount, COUNTIF(id, keyId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    cols, SEQUENCE(, maxCount),
    header, HSTACK("ID", "S/N "&cols),
    results, HSTACK(keyId, WRAPROWS(DROP(SORT(VSTACK(data, EXPAND(TOCOL(IFS(newRows>=cols, keyId), 2),, 2, ""))),, 1), maxCount)),
    VSTACK(header, results)
)

 

Adjust the data range as necessary, and the rest will take care of itself. No iterative functions were used here... the main concept is to expand and sort the dataset, so all Primary IDs contain the same number of related records (new records are filled with ""), which are then sent to a single row using WRAPROWS.

 

When tested with 5000 rows of data, there was no noticeable lag. When tested with 25000 rows of data, the lag time was less than a second.

 

Example ResultsExample Results

 

Note: the formula shown above assumes there are no blank rows within the dataset. If blank rows exist, the formula can be modified as follows:

 

=LET(
    data, A2:B5001,
    id, INDEX(data,, 1),
    keyId, SORT(TOCOL(UNIQUE(id), 1)),
    recordCount, COUNTIF(id, keyId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    cols, SEQUENCE(, maxCount),
    header, HSTACK("ID", "S/N "&cols),
    results, HSTACK(keyId, WRAPROWS(DROP(SORT(VSTACK(FILTER(data, id<>""), EXPAND(TOCOL(IFS(newRows>=cols, keyId), 2),, 2, ""))),, 1), maxCount)),
    VSTACK(header, results)
)

@thepinkbird 

For the future - A variant with GROUPBY and some eta.

=GROUPBY(Table1[Tag],Table1[SN],ARRAYTOTEXT,,0)