Help with Generating Number Combinations [SOLVED]

[ascheetz1]

Hi - I am trying to figure out if there is a way to set up an excel sheet so that it lists all of the different ways x numbers can be combined in a comprehensive way. I know that's a little unclear so here is an example assuming x = 4.

1 2 3 4
12 3 4
12 34
13 2 4
13 24
14 2 3
14 23
123 4
124 3
134 2
1 234
1234
1 23 4
1 24 3
1 2 34
1 234
12 34

Maybe easier to see with brackets denoting numbers that a paired together:
[1] [2] [3] [4]
[12] [3] [4]
[12] [34]
[13] [2] [4]
[13] [24]
[14] [2] [3]
[14] [23]
[123] [4]
[124] [3]
[134] [2]
[1] [234]
[1234]
[1] [23] [4]
[1] [24] [3]
[1] [2] [34]
[1] [234]
[12] [34]

Each of the above rows includes all of numbers 1,2,3,4, with no duplicates of any number in a row, and different combinations of individual vs. linked numbers are distinct combinations. I think this is a comprehensive list for x=4 but I may have missed some. Doing this manually becomes increasingly onerous for x>4.

Is there a way to produce a list of these combinations in excel? Obviously understand there is likely an upper limit because the # of possible combinations grows exponentially, but is it possible for x=7 for example?

[FlameRetired]

I'm working on this. At the moment I do not see a solution, but will stick with it.

One thing I do notice though you have a duplication in rows 3 and 17. So there are actually 2^4 =16 combinations if I understand you correctly. Sounds like trumpet key/note combinations.

[FlameRetired]

BTW you mentioned " but is it possible for x=7 for example? " specifically.

By chance are you trying to work out custom weekends for the functions NETWORKDAYS.INTL or WORKDAY.INTL? They are designated by 7 digit strings of 1s and 0s. If so that can be done and it is one of the approaches I am considering.

[ascheetz1]

No but that's interesting! 7 is as high as I think I'll need but not days of the week-related.

[ascheetz1]

Figured out a VBA solution with the help of Chat GPT-4. I'm sure this is not close to the most efficient solution but it should work for my purposes. Posting here for reference for anyone interested - all that's required is a named cell in the workbook called NumItems which equals a number "x" in the above example. Runs quickly enough at NumItems as high as X=8 - slow at X=9 and wouldn't recommend higher than that.

Please Login or Register  to view this content.

[FlameRetired]

Glad you found solution.

Sorry I didn't help. I couldn't find a formula solution that distinguished between [1234] in row 12 and [12] [34] in the last row.