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

[Rummers]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Screen Left Lower Wall[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Screen Left Upper Wall[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Screen Middle Lower Wall[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Screen Middle Upper Wall[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Screen Right Lower Wall[/TD]
[TD]5[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Screen Right Upper Wall[/TD]
[TD]6[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Eric W]

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:

	A	B	C	D	E	F	G	H	I
2 screens	3 screens	4 screens	5 screens	6 screens	Screen numbers
X
X
X
	X
X
X
	X
X
	X
	X
		X
X
X
	X
X
	X
	X
		X
X
	X
	X
		X
	X
		X
		X
			X
X
X
	X
X
	X
	X
		X
X
	X
	X
		X
	X
		X
		X
			X
X
	X
	X
		X
	X
		X
		X
			X
	X
		X
		X
			X
		X
			X
			X
				X

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]000000[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]000001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]000010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]000011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]56[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]000100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]000101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]46[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]000110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]000111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]456[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]001000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]001001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]36[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]001010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]35[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]001011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]356[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]001100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]34[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]001101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]346[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]001110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]345[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]001111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3456[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]010000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]010001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]26[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]010010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]010011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]256[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]010100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]010101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]246[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]010110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]245[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]010111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2456[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]011000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]011001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]236[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]011010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]235[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]011011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2356[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]011100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]234[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]011101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2346[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]011110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2345[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]011111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]23456[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]100000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]100001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]100010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]100011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]156[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]100100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]100101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]146[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]100110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]145[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]100111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1456[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]101000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]101001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]136[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]101010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]135[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]101011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1356[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]101100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]134[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]101101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1346[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]101110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1345[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]101111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]13456[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]110000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]110001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]126[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]110010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]110011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1256[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]110100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]124[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]110101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1246[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]110110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1245[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]110111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12456[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]111000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]111001[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1236[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]111010[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1235[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]111011[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12356[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]111100[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1234[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]111101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12346[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]111110[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12345[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]111111[/TD]

[TD="align: right"][/TD]
[TD="align: right"]123456[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=TEXT(DEC2BIN(A3),"000000")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF(LEN(SUBSTITUTE($B3,"0",""))=COLUMNS($B3:C3),"X","")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=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","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[Eric W]

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.