Forum Discussion

leandertorres's avatar
leandertorres
Copper Contributor
Aug 07, 2024

need help transforming data

Hi,   I basicly have the left table and want to create the right one but with a larger data set. Annybody knows how to do this in Excel? Does this proces have a name?
excel
djclements's avatar
djclements
Silver Contributor
Aug 07, 2024

leandertorres What version of Excel are you working with? If you have Excel for MS365, you could try either of the following array transformations:

 

=LET(
    array1, A5:A8,
    array2, TOROW(B5:B8),
    HSTACK(TOCOL(IF({1}, array1, array2)), TOCOL(IF({1}, array2, array1)))
)

 

-OR-

 

=LET(
    arr, LAMBDA(n, CHOOSE(n, A5:A8, TOROW(B5:B8))),
    HSTACK(TOCOL(arr({1})), TOCOL(arr({2})))
)

 

Or, if you need the ability to combine two multi-column arrays or tables:

 

=LET(
    array1, A5:A8,
    array2, B5:B8,
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

 

See attached...