Forum Discussion

Wee001's avatar
Wee001
Copper Contributor
Oct 29, 2023

Question on Merging of rows

Hi, I have a sheet of over 5000 rows, and I'm looking to merge rows with a similar name, while combining the rows so that main row contains all the necessary information. Not really sure how to expla...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 30, 2023

Wee001 

A 365 solution with the source data tabled:

 

 

 

=LET(
    header, Table1[#Headers],
    fullname, Table1[First Name] & " " & Table1[Last name],
    names, SORT(UNIQUE(fullname)),
    Consolidate, LAMBDA(a, v,
        LET(
            filtered, N(
                FILTER(Table1[[Address]:[Supporting Docs]], fullname = v)
            ),
            TF_array, BYCOL(
                filtered,
                LAMBDA(col, IF(SUM(col) >= 1, TRUE, FALSE))
            ),
            splitname, TEXTSPLIT(v, " "),
            VSTACK(a, HSTACK(splitname, TF_array))
        )
    ),
    REDUCE(header, names, Consolidate)
)

Resources