Need assistance in having contents of one column encompass all contents of another

New Contributor

Hello,

 

I am looking to build out functionality to have each cell in "Alpha" connect to all cells in "Beta", just like the example below. This needs to be flexible enough to adjust for varying amounts of data in alpha and beta. I am posting here to figure out the best way to build out this functionality and formatting. 

Example.PNG

4 Replies

@Domcach If you are willing to dive into a solution with Power Query, the attached workbook may contain just what you need, although it repeats all entries in Alpha after joining them with all entries in Beta.

Add new items at the bottom of each of the blue tables. On the Data ribbon press Refresh All and the green table will update almost instantly.

@Domcach 

As variant

=LET(
      alpha,        Table1[Alpha],
      beta,         Table2[Beta],
      a,            COUNTA(alpha),
      b,            COUNTA(beta),
      k,            SEQUENCE(a*b),
      aInd,         (k-1)/b+1,
      bInd,         MOD(k-1,b)+1,
      firstColumn,  IF(INT(aInd)+1=aInd+1, INDEX(alpha, aInd), ""),
      secondColumn, INDEX(beta, bInd),
      IF( {1,0}, firstColumn, secondColumn)
)
Thank you! I will work through it and see how it goes