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)))))

@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])))))

@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.

@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. 😅

@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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!