bansche123
New Member
- Joined
- Dec 9, 2019
- Messages
- 13
- Office Version
- 365
- Platform
- Windows
Dear Forum Users,
In the uploaded Mini sheet there is a table titled: “ALL possible combinations of NUMBERS”(AZ13:BE22). In this table I would like for combinations of NUMBERS to appear after the comparison of the two SET combinations. This means that in the number combinations there shall be numbers from:
Basically a number from each SET, must be in the NUMBER combination. Also, maximally, only, one number from each set must be used. In this example I used yellow background to show that in each set of both SET combinations there is a number the forms the NUMBER combination: 1, 11, 24, 30, 31. Now, instead of 24, I could have also picked 8 and 45 and crossed out 24. Then the combination would consist of 6 numbers: 1, 8, 11, 30, 31, 45.
Any help would be appreciated
Please don’t hesitate do ask questions of something isn’t quite understandable.
In the uploaded Mini sheet there is a table titled: “ALL possible combinations of NUMBERS”(AZ13:BE22). In this table I would like for combinations of NUMBERS to appear after the comparison of the two SET combinations. This means that in the number combinations there shall be numbers from:
- “Identical numbers with 2nd combination plus green (Tab. 1)” (BA6:BE11) and
- “Identical numbers with 1st combination plus green (Tab. 2)” (BA27:BE32)
Basically a number from each SET, must be in the NUMBER combination. Also, maximally, only, one number from each set must be used. In this example I used yellow background to show that in each set of both SET combinations there is a number the forms the NUMBER combination: 1, 11, 24, 30, 31. Now, instead of 24, I could have also picked 8 and 45 and crossed out 24. Then the combination would consist of 6 numbers: 1, 8, 11, 30, 31, 45.
all possible combinations.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
AZ | BA | BB | BC | BD | BE | |||
3 | Identical numbers with 2nd combination plus | |||||||
4 | ||||||||
5 | green (Tab. 1) | |||||||
6 | 1 | 24 | 45 | |||||
7 | 2 | 31 | ||||||
8 | 5 | 11 | ||||||
9 | 9 | 15 | 30 | |||||
10 | 12 | 1 | ||||||
11 | 15 | |||||||
12 | ||||||||
13 | ALL possible combinations of NUMBERS | |||||||
14 | ||||||||
15 | I | II | III | IV | V | VI | ||
16 | 1 | 11 | 24 | 30 | 31 | |||
17 | 1 | 8 | 11 | 30 | 31 | 45 | ||
18 | ||||||||
19 | ||||||||
20 | ||||||||
21 | ||||||||
22 | ||||||||
23 | ||||||||
24 | Identical numbers with 1st combination plus | |||||||
25 | ||||||||
26 | green (Tab. 2) | |||||||
27 | 1 | 8 | 24 | |||||
28 | 3 | 31 | ||||||
29 | 4 | 30 | ||||||
30 | 7 | 1 | ||||||
31 | 10 | 11 | ||||||
32 | 15 | |||||||
combining numbers |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AZ6:AZ11,AZ27:AZ32 | AZ6 | =AS6 |
BA6:BE10 | BA6 | =IF(COUNTIFS($O$27:$S$40,INDEX(O$6:O$19,$AZ6)),IFERROR(1/(1/SUMPRODUCT($O$27:$S$40,ISNUMBER(MATCH($N$27:$N$40,$AZ$27:$AZ$31,))*($O$27:$S$40=INDEX(O$6:O$19,$AZ6)))),""),INDEX(O$6:O$19,$AZ6)) |
BA11 | BA11 | =IF(OR(AT11=AT32,AT11=AT27:AX32),AT11,"") |
BB11 | BB11 | =IF(OR(AU11=AU32,AU11=AT27:AX32),AU11,"") |
BC11 | BC11 | =IF(OR(AV11=AV32,AV11=AT27:AX32),AV11,"") |
BD11 | BD11 | =IF(OR(AW11=AW32,AW11=AT27:AX32),AW11,"") |
BE11 | BE11 | =IF(OR(AX11=AX32,AX11=AT27:AX32),AX11,"") |
BA27:BE31 | BA27 | =IF(COUNTIFS($O$6:$S$19,INDEX(O$27:O$40,$AZ27)),IFERROR(1/(1/SUMPRODUCT($O$6:$S$19,ISNUMBER(MATCH($N$27:$N$40,$AZ$6:$AZ$10,))*($O$6:$S$19=INDEX(O$27:O$40,$AZ27)))),""),INDEX(O$27:O$40,$AZ27)) |
BA32 | BA32 | =IF(OR(AT32=AT11,AT32=AT6:AX11),AT32,"") |
BB32 | BB32 | =IF(OR(AU32=AU11,AU32=AT6:AX11),AU32,"") |
BC32 | BC32 | =IF(OR(AV32=AV11,AV32=AT6:AX11),AV32,"") |
BD32 | BD32 | =IF(OR(AW32=AW11,AW32=AT6:AX11),AW32,"") |
BE32 | BE32 | =IF(OR(AX32=AX11,AX32=AT6:AX11),AX32,"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AZ27:AZ32 | Cell Value | <15 | text | NO |
AZ6:AZ11 | Cell Value | <15 | text | NO |
AZ27:AZ32 | Cell Value | =15 | text | NO |
AZ6:AZ11 | Cell Value | =15 | text | NO |
Any help would be appreciated
Please don’t hesitate do ask questions of something isn’t quite understandable.