Mar 12 2021 01:36 PM
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.
Mar 12 2021 10:50 PM - edited Mar 12 2021 11:44 PM
@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.
Mar 13 2021 06:21 AM
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)
)
Mar 15 2021 09:20 AM
Mar 15 2021 09:20 AM