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
djclements's avatar
djclements
Bronze Contributor
Mar 09, 2024

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

Resources