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

Question

Hello,&nbsp;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.&nbsp;

riny_van_eekelen · Answer

Domcach&nbsp;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.

sergeibaklan · Answer

Domcach&nbsp;
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)
)

domcach · Answer

Thank you! I will work through it and see how it goes

domcach · Answer

Thank you, will see if this works

Forum Discussion

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

4 Replies