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

thepinkbird 

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

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

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 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 

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

thepinkbird 

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

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 )
 )
)