How to conditionally use function TOROW

[viva4567]

I've been given a csv file with multiple rows of data for each account. Most have two rows of data and I've been able to learn how to use TOROW to move all that into one row so I can further sort and analyze the data. Some rows have an additional row of that needs to handled in the same way, that is, appending the third row to the single new row created by TOROW. I don't know how to do this. A sample of the data format is below.
My Excel skills are pretty basic so if anyone can offer help, please keep that in mind.

Many thanks!

 

[hagia_sofia]

Hello, test the following to see if it is of any use:

=LET(
array,SUBSTITUTE(A26:E34,"",""),
a,A26:A34,
b,SCAN(0,--(a<>""),LAMBDA(a,b,a+b)),
c,IFNA(DROP(REDUCE("",UNIQUE(b),LAMBDA(x,y,VSTACK(x,TOROW(FILTER(array,b=y))))),1),""),
d,ISNUMBER(--(c)),
IF(d=TRUE,c*1,c))

 

[djclements]

An alternative approach:

If the number of rows per record was consistent throughout the entire dataset, you could simply use WRAPROWS-TOCOL. For example, if the number of rows was always 2, the generic syntax would be:

=WRAPROWS(TOCOL(range), COLUMNS(range)*2)

The same approach can also be used when the number of rows per record varies by "padding" the dataset with blank rows, so each record contains the same number of rows. With your sample data, the following formula could be used:

=LET(
    data, A26:E34,
    names, TAKE(data,, 1),
    id, SCAN(1, names, LAMBDA(a,v, a+(v<>""))),
    keyIds, UNIQUE(id),
    rowCount, MMULT(N(keyIds=TOROW(id)), id^0),
    maxCount, MAX(rowCount),
    newIds, TOCOL(IFS(maxCount-rowCount>=SEQUENCE(, maxCount), keyIds), 2),
    resize, SORTBY(EXPAND(IF(ISBLANK(data), "", data), ROWS(id)+ROWS(newIds),, ""), VSTACK(id, newIds)),
    WRAPROWS(TOCOL(resize), COLUMNS(data)*maxCount)
)

There are many variations this approach can take depending on the situation and the desired output. Cheers!