Permutation Generator
Hey Community  I couldn't score enough time at the formulas booth this week to pick someone's brain on this one. I am looking to recreate an Excel tool that I use to create all permutations from a two column matrix, for example, if I have two columns called SIZE and SHAPE, the next column should be a column formula produces this:
I'm going in a circle because Excel has a selfreferencing ROW() function, but Smartsheet does not. I'm not skilled enough with Index(Join(Collect(Distinct(Etc. to build something that references itself. Anyone have a creative idea?
For reference, the Excel version looks like this (assume the column formula would be in column C)
"=IF(ROW()ROW($C$1)+1>COUNTA(A:A)*COUNTA(B:B),"", INDEX(A:A,INT((ROW()ROW($C$1))/COUNTA(B:B)+1))&""&INDEX(B:B,MOD(ROW()ROW($C$1),COUNTA(B:B))+1))"
Best Answer

This was a fun challenge, and I have kept coming back to it since seeing your post a few days ago. I really wanted to find a solution using only the information you provided, but I simply cannot find a way to do this without some type of helper column.
With the addition of a "sequential number" column, the following should work. I try to avoid autonumber columns when possible, so I do this manually. You can simply add a few hundred rows and drag down to ensure you have enough. This should work with any number of Size and Shape.
=IF(Number@row <= COUNTIFS(Size:Size, NOT(ISBLANK(@cell))) * COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))), INDEX(COLLECT(Size:Size, Size:Size, NOT(ISBLANK(@cell))), (CEILING(Number@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))))) / COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))) + "" + INDEX(COLLECT(Shape:Shape, Shape:Shape, NOT(ISBLANK(@cell))), IF(MOD([Number]@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))) = 0, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))), MOD([Number]@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))))))
This could be made shorter using COUNT instead of COUNTIFS several times, but I prefer the COUNTIFS solution since it is more tolerant to cells with stray information.
Answers

This was a fun challenge, and I have kept coming back to it since seeing your post a few days ago. I really wanted to find a solution using only the information you provided, but I simply cannot find a way to do this without some type of helper column.
With the addition of a "sequential number" column, the following should work. I try to avoid autonumber columns when possible, so I do this manually. You can simply add a few hundred rows and drag down to ensure you have enough. This should work with any number of Size and Shape.
=IF(Number@row <= COUNTIFS(Size:Size, NOT(ISBLANK(@cell))) * COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))), INDEX(COLLECT(Size:Size, Size:Size, NOT(ISBLANK(@cell))), (CEILING(Number@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))))) / COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))) + "" + INDEX(COLLECT(Shape:Shape, Shape:Shape, NOT(ISBLANK(@cell))), IF(MOD([Number]@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))) = 0, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell))), MOD([Number]@row, COUNTIFS(Shape:Shape, NOT(ISBLANK(@cell)))))))
This could be made shorter using COUNT instead of COUNTIFS several times, but I prefer the COUNTIFS solution since it is more tolerant to cells with stray information.

@Carson Penticuff  You nailed it, thank you! I have no problem with helper columns or dragging down. I had never noticed or used the Ceiling formula before. Well played!

@Carson Penticuff This one caught my attention too (although I was late to see it).
This is what I came up with:
=IF(Number@row <= COUNT(Size:Size) * COUNT(Shape:Shape), INDEX(Size:Size, ROUNDUP(Number@row / COUNT(Shape:Shape))) + "" + INDEX(Shape:Shape, IF(MOD(Number@row, COUNT(Shape:Shape)) = 0, COUNT(Shape:Shape), MOD(Number@row, COUNT(Shape:Shape)))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome I completely missed the opportunity for the ROUNDUP()! I think I also went a little overboard with the COLLECT and COUNTIFS. I like your solution, very classy!

And with a little bit of fiddling... 3 Variables (still using the Number column). I think I see a pattern too that should make it relatively straightforward to add more variables as well.
=IF(Number@row <= COUNT([Project Size]:[Project Size]) * COUNT([Schedule Health]:[Schedule Health]) * COUNT([Budget Health]:[Budget Health]), INDEX([Project Size]:[Project Size], ROUNDUP(Number@row / (COUNT([Schedule Health]:[Schedule Health]) * COUNT([Budget Health]:[Budget Health])))) + "" + INDEX([Schedule Health]:[Schedule Health], IF(MOD(ROUNDUP(Number@row / COUNT([Budget Health]:[Budget Health])), COUNT([Budget Health]:[Budget Health])) = 0, COUNT([Budget Health]:[Budget Health]), MOD(ROUNDUP(Number@row / COUNT([Budget Health]:[Budget Health])), COUNT([Budget Health]:[Budget Health])))) + "" + INDEX([Budget Health]:[Budget Health], IF(MOD(Number@row, COUNT([Budget Health]:[Budget Health])) = 0, COUNT([Budget Health]:[Budget Health]), MOD(Number@row, COUNT([Budget Health]:[Budget Health])))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Carson Penticuff @Paul Newcome  Interesting... I was planning to build it out using "unlimited" variables by setting up multiple 2 column versions, and then an additional variable column where the first is the concatenated value from the first pair. My intent is to keep the formulas as short as possible for easy maintenance:

@Carson Penticuff I started out with
COUNTIFS(Column:Column, @cell <> "")
but then I realized, the basic COUNT function excludes blanks anyway. I like less typing when I can, so I adjusted there.
I almost never use the CEILING function, but looking at it now, I may be able to simplify my 3 variable solution a bit with that one instead of the ROUNDUP/MOD combo. Looks like I have some more playing to do. Haha.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Scott Peters That's a great idea too. Use the formula to create from the first two then use that output and the third variable as the two variables using the same formula.
Repeating that allows for a much wider variation without having to use different formulas (other than column references).
I like that idea and might just have to steal it. Haha.
.
But I am still going to test out my own version of a 4 variable to see if my thought on the pattern is right. 😅
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome I keep coming back to that extra IF statement to account for the cases where MOD results in zero. I feel like there should be a way to avoid that, but I am drawing a blank.

@Carson Penticuff I keep coming back to that too, but I think at this point I am too deep into it to think of something different. I am going to try to revisit it next week.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!