3

I am trying to create a loop in excel with formulas only. What I am trying to achieve is described below. Let's say I have 3 columns as inputs: (i) Country; (ii) variable; and (iii) year. I want to expand from these inputs to then assign values to these parameters.

Inputs:

Country        Variable                Year
GB               GDP                   2015
DE              area                   2016

Outputs:

Country       Variable         Year
GB              GDP            2015  
GB              GDP            2016
GB              area           2015
GB              area           2016
DE              GDP            2015
DE              GDP            2016 
DE              area           2015 
DE              area           2016

How can I do that efficiently using Excel?

Many thanks

Peslier53
  • 587
  • 1
  • 7
  • 21
  • 1
    See here: https://superuser.com/questions/1194312/excel-create-table-with-all-combinations/1194380#1194380 You can pull the formula apart to put each part in its own cell. – Scott Craner Feb 18 '22 at 16:01
  • 1
    `=LET( matrix, A2:C3, cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ), rC, ROWS( matrix ), rSeq, SEQUENCE( rC ), eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ), m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ), m)` – mark fitzpatrick Feb 18 '22 at 16:08
  • 1
    I'm not so sure this was really a duplicate question, but there are some nearly identical questions, for sure... Did you want the result to avoid duplicate outcomes? e.g. if there is a row with CH | area | 2015, the formulas above will create 27 rows with lots of duplicates such as DE | GDP | 2015 repeated 4x. If you don't want row-duplication, then there is a way to change the formula I posted. – mark fitzpatrick Feb 18 '22 at 16:45
  • 1
    I'd say unpivot in power query would be the way. – P.b Feb 18 '22 at 17:30
  • 1
    @mark to workaround the duplicates as pointed out, you could simply end your `LET`-function with `UNIQUE(m)` – P.b Feb 19 '22 at 17:34
  • 1
    Hey @P.b - funny. I had just opened this question to add a revised formula and your comment appeared just as I did. I was thinking the same, but I am also thinking that this question could be re-framed and re-posted. So here is the revised version with the exact recommendation that you just made: – mark fitzpatrick Feb 19 '22 at 17:39
  • 1
    `=LET( matrix, A2:E6, cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ), rC, ROWS( matrix ), rSeq, SEQUENCE( rC ), eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ), unblank, IF( ISBLANK(matrix), "°|°", matrix ), m, UNIQUE( INDEX( unblank, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ) ), FILTER( m, BYROW( IFERROR( FIND( "°|°", m ), 0 ), LAMBDA(x, SUM( x ) ) ) = 0 ) )` – mark fitzpatrick Feb 19 '22 at 17:39
  • But the OP asked to do it *efficiently* and this formula is not efficient, so I am thinking about re-posting with a better formula that doesn't fit nicely into a comment (not that the one above does either). – mark fitzpatrick Feb 19 '22 at 17:41
  • 2
    I was more thinking in the direction: `=LET( matrix, A2:C3, cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ), rC, ROWS( matrix ), rSeq, SEQUENCE( rC ), eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ), m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ), UNIQUE(m))` – P.b Feb 19 '22 at 17:49
  • 1
    @P.b - that was my first approach, but then I realized that there is a problem with blank cells being picked up as 0's. So I replaced them with the improbably °|° and then extracted any row that contained that improbable string. In the end, UNIQUE is cleaning up the output, but there is a filter as well. The problem with this approach is that it generates all permutations with repetition, so if the columns contain 100 X 100 X 100 elements, it will generate 1m rows that will then get filtered down to perhaps 20% of the output. *i.e. NOT(efficient)* :-D – mark fitzpatrick Feb 19 '22 at 18:59
  • 1
    Very well. I think that the OP states efficiënt as in not manual. But I like the way this leads to nice approaches like this. – P.b Feb 19 '22 at 19:37
  • 2
    @Peslier53 - Your question has been re-posted [here](https://stackoverflow.com/questions/71188880/generate-all-permutations-in-excel-using-lambda/71189297#71189297) and may or may not be answered the way that you want. But it will definitely require Office 365 with the LAMBDA update that came out this month. ***Nice question in any case.*** – mark fitzpatrick Feb 19 '22 at 21:30

0 Answers0