Permutation Generator

Scott Peters

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 re-create 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 self-referencing 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))"

Carson Penticuff

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

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.

Scott Peters

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

Paul Newcome

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

Carson Penticuff

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

Paul Newcome

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

Scott Peters

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

Paul Newcome

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

Paul Newcome

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

Carson Penticuff

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

Paul Newcome

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