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 contain data from Table 2 repeated 200times (20 000 rows)

then I need to assign unique value from Table 1 to each 100 rows.

 

Basicaly I need to create 200 groups of 100 rows, each group needs to have an one unique value assigned from Table 1.

 

How migh I achieve this ?

 

Thank you

Marek

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
      Bronze 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! 🙂

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 25, 2024

    MarekFOLO 

    Sub table3()

Dim i, j, k As Long

Range("D:E").Clear

k = 0
For i = 2 To 101
For j = 2 To 201
Cells(j + k, 4).Value = Cells(i, 2).Value
Cells(j + k, 5).Value = Cells(j, 1).Value

Next j
k = k + 200
Next i


End Sub

    In the attached file you can click the button in cell I2 to run this macro which returns a list in columns D and E with the values from columns A and B.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 25, 2024

    MarekFOLO 

    An alternative could be Power Query. In the attached file you can change the entries in the blue dynamic tables. 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.

     

    The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.

Resources