Create new lists from an array in Excel

Question

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" &amp; "c" and populate the list with the corresponding&nbsp; Col 2 valuee.g.&nbsp;Col1&nbsp; &nbsp; &nbsp;Col2&nbsp; &nbsp; &nbsp; &nbsp; new list "a"&nbsp; &nbsp; &nbsp;new list "b"&nbsp; &nbsp; new list "c"a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp;&nbsp;b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;Thanks All

hansvogelaar · Answer

JCK01&nbsp;
See the attached demo workbook.

oliverscheurich · Answer

JCK01&nbsp;An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

jck01 · Answer

HansVogelaar&nbsp;&nbsp;Many thanks!

sergeibaklan · Answer

JCK01&nbsp;
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, "" ) )
)

Forum Discussion

Create new lists from an array in Excel

4 Replies

Resources