Forum Discussion

Copper Contributor

Apr 19, 2024

Create new lists from an array in Excel

Hi, I would like help building a formula that creates a new list of values from an array containing a reference value in example below find all "a", "b" & "c" and populate the list with the correspo...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Apr 20, 2024

JCK01

As variant

=LET(
    first, CHOOSECOLS(data, 1),
    values, CHOOSECOLS(data,2),
    header, TRANSPOSE( UNIQUE( first )),
    clear, LAMBDA(col, TOCOL( IF(col=0, NA(), col ),3 ) ),
    byHeader, (first=header)*values,
    res, DROP(
        REDUCE("", SEQUENCE(COLUMNS(header)),
            LAMBDA(a,v,
                HSTACK(a, clear(CHOOSECOLS(byHeader,v) )
                )
            )
        ),,1),
    VSTACK( header, IFNA( res, "" ) )
)

Create new lists from an array in Excel.xlsx16 KB