Non Repeating combinations of numbers (1-6 from 2 to 6 used)

Rummers

New Member
Joined
Aug 9, 2016
Messages
7
Hi,

I am trying to find a way to identify and display the combinations of 1,2,3,4,5,6 using 2 numbers, 3 numbers all the way up to 6 numbers, i.e.

2 numbers = 1,2 - 1,3 - 1,4 - 2,3 etc
3 numbers = 1,2,3 - 1,2,4 - 1,2,5 etc
6 numbers = 1,2,3,4,5,6 (I do not need 6,5,4,3,2,1 or any other variation in order of numbers)

Each number refers to a 'Screen' in this instance and has a unique value, this will then be programmed into a system to determine which images go to which 'Screen'.

As an example...

NameNumberValue
Screen Left Lower Wall11
Screen Left Upper Wall22
Screen Middle Lower Wall34
Screen Middle Upper Wall48
Screen Right Lower Wall516
Screen Right Upper Wall632

<tbody>
</tbody>

The combinations will then be used as follows...

Number 1,2 = Value 1+2 = Total Value 3 (any image with a value of 3 will go to both screen 1 & 2)
Number 1,2,4 = Value 1+2+8 = Total Value 11 (any image with a value of 11 will go to screens 1,2 & 4)

And so on for every variation of 1 to 6 digits (but as mentioned earlier duplicates that are just in a different order are useless, 1,2 is fine but then 2,1 is not needed).

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,780
There's an easy way to figure out all the combinations, just count in binary. Excel has a built-in function to do that. For example:

ABCDEFGHI
1
22 screens3 screens4 screens5 screens6 screensScreen numbers
30000000
410000016
520000105
63000011X56
740001004
85000101X46
96000110X45
107000111X456
1180010003
129001001X36
1310001010X35
1411001011X356
1512001100X34
1613001101X346
1714001110X345
1815001111X3456
19160100002
2017010001X26
2118010010X25
2219010011X256
2320010100X24
2421010101X246
2522010110X245
2623010111X2456
2724011000X23
2825011001X236
2926011010X235
3027011011X2356
3128011100X234
3229011101X2346
3330011110X2345
3431011111X23456
35321000001
3633100001X16
3734100010X15
3835100011X156
3936100100X14
4037100101X146
4138100110X145
4239100111X1456
4340101000X13
4441101001X136
4542101010X135
4643101011X1356
4744101100X134
4845101101X1346
4946101110X1345
5047101111X13456
5148110000X12
5249110001X126
5350110010X125
5451110011X1256
5552110100X124
5653110101X1246
5754110110X1245
5855110111X12456
5956111000X123
6057111001X1236
6158111010X1235
6259111011X12356
6360111100X1234
6461111101X12346
6562111110X12345
6663111111X123456

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B3=TEXT(DEC2BIN(A3),"000000")
C3=IF(LEN(SUBSTITUTE($B3,"0",""))=COLUMNS($B3:C3),"X","")
I3=IF(LEFT(B3)="1","1","")&IF(MID(B3,2,1)="1","2","")&IF(MID(B3,3,1)="1","3","")&IF(MID(B3,4,1)="1","4","")&IF(MID(B3,5,1)="1","5","")&IF(MID(B3,6,1)="1","6","")

<tbody>
</tbody>

<tbody>
</tbody>



Column A just counts up from 0 to 63. Column B uses Excel's DEC2BIN (Decimal to Binary) function to convert that number to binary. Then going from left to right, each digit in column B represents a screen. So on row 14, the value is 11, the binary representation is 001011, which means: use screens 3, 5, and 6. Then you can put the formula in C3, and copy it to the rest of the C3:G66 range. The I4 formula converts the binary representation to the screen number representation.

Hope this helps!

Edit: After looking at this again, I see that I've got the screen numbers/values reversed from your example. That's easily changed in the I3 formula like this:

=IF(MID(B3,6,6)="1","1","")&IF(MID(B3,5,1)="1","2","")&IF(MID(B3,4,1)="1","3","")&IF(MID(B3,3,1)="1","4","")&IF(MID(B3,2,1)="1","5","")&IF(MID(B3,1,1)="1","6","")
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,780
Urk, that last formula should be:

=IF(MID(B3,6,1)="1","1","")&IF(MID(B3,5,1)="1","2","")&IF(MID(B3,4,1)="1","3","")&IF(MID(B3,3,1)="1","4","")&IF(MID(B3,2,1)="1","5","")&IF(MID(B3,1,1)="1","6","")

although it works as written due to a fluke.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,772
Messages
5,470,692
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top