Forum Discussion

MarekFOLO's avatar
MarekFOLO
Copper Contributor
Mar 25, 2024

Creating table from 2 tables (20 000 rows)

Hello I'm looking for help with a problem I'm working on.   I have 2 tables, Table 1 has 200 unique values (in rows) Table 2 has 100 unique values (in rows) I need to create Table 3 that will c...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 25, 2024

MarekFOLO 

This assumes your tables are single column arrays

= LET(
    content, TOCOL(IF(ISTEXT(table1), TOROW(table2))),
    header,  TOCOL(IF(ISTEXT(TOROW(table2)), table1)),
    HSTACK(header, content)
  )
  • djclements's avatar
    djclements
    Silver Contributor
    Mar 26, 2024

    Hi PeterBartholomew1, here's a multi-purpose LAMBDA function (along the same lines as what you've shared), which can be used to generate all possible permutations and combinations, as well as to merge tables and columns:

     

    PERMA:
=LAMBDA(n,[r],[table_mode],
    IF(
        NOT(table_mode),
        IFS(r=0, 1, r=1, SEQUENCE(n), TRUE, PERMA(IF(SEQUENCE(, r), SEQUENCE(n)),, TRUE)),
        IF(
            ISOMITTED(r),
            REDUCE(CHOOSECOLS(n, 1), SEQUENCE(COLUMNS(n)-1,, 2), LAMBDA(p,c, PERMA(p, CHOOSECOLS(n, c), TRUE))),
            LET(
                a, SEQUENCE(ROWS(n)),
                b, SEQUENCE(, ROWS(r)),
                HSTACK(
                    CHOOSEROWS(n, TOCOL(IF(b, a))),
                    CHOOSEROWS(r, TOCOL(IF(a, b)))
                )
            )
        )
    )
)

     

    For this scenario, it can be used to combine two tables with multiple columns by setting the table_mode to TRUE:

     

    =PERMA(Table1, Table2, TRUE)

     

    Check out the attached workbook, if desired, to see how else it can be used... Cheers! 🙂

Resources