Formula for combining numbers from 2 tables

[bansche123]

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:

• “Identical numbers with 2nd combination plus green (Tab. 1)” (BA6:BE11) and
• “Identical numbers with 1st combination plus green (Tab. 2)” (BA27:BE32)

This is a graphic demonstration how the formula should make the combinations of numbers:

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.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please supply all relevant link(s)

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[bansche123]

Cross posted at: Formula for creating number combinations from two tables
(last post)

 

[Fluff]

And the other sites.

 

[bansche123]

Cross posted at: Formula for combining numbers from 2 tables

 

[bansche123]

UPDATE: I have a newer Excel version – the 365 one, not the 2016 anymore.