A feature to create pairs of combinations

[bansche123]

Dear Forum Users,

In the file “Combi Pairs” there are three worksheets.

The first is titled “1st combi”. In this worksheet you can find combinations in columns ranging from B to G. The combinations consist of sets numbered from 1 to 14. S1 means the first set, S2 the second and so on. So, technically, the combinations should look like, for instance — S1, S2, S5 instead of just 1, 2, 5. That is why in the A column there is an “S” before each combination, just as a reminder that the numbers in the combination indicate sets. The 14 sets are to be found in columns from I to V. Each of the 14 sets has got six slots for numbers. The sets are filled with different amounts of numbers while some aren’t filled with any. D1 and G1 are also sets of numbers however, these are separate sets and will be described later in more detail.

The second worksheet is titled “2nd combi” and in its structure it’s identical to the “1st combi” worksheet. The differences are:

• the combinations — there are more and varying combinations in comparison to the “1st combi” worksheet
• the numbers in the 14 sets — there are differently distributed among the sets, as well as there are new/other numbers, that weren’t present in the sets in the “1st combi” worksheet
• both sets D1 and G1 have different numbers inside of them

The third worksheet is titled “Pairs”. That will be the whole point of my thread. In the future in this worksheet I would like to make pairs of combinations (one combination from “1st combi” and one combination from “2nd combi”) appear. Obviously there are going to be conditions under which these pairs shall be formed. I will describe these requirements below.

"Pairing", does not mean directly merging or combining but simply displaying the two combinations together but separately, e.g. with a "+" sign in between.

The pairing of two combinations must take place under some conditions and this is exactly what the numbers in the Sets are necessary for. I will try to explain the whole process with an example. Let's say the feature has to decide whether the combination 1, 3, 5, 10 (I combination) from “1st combi” (yellow background, 25th row) may be paired with combination 2, 3, 9, 13 (II combination) from “2nd combi” (yellow background, 65th row). To determine this, the feature must compare the numbers assigned to the Sets: S1, S3, S5, S10 in worksheet “1st combi” with the numbers assigned to the Sets: S2, S3, S9, S13 in “2nd combi”. I have created a table with the comparison. (attach. Tab. 1 ENG)

A number of things can arise here:

1. The first step is to compare if and which numbers occur in the sets, that both combinations are made of. In principle, the basic requirement to form a pair is that — in each Set of the first combination, at least one number occurs, that is found in a Set of the second combination. The numbers with a yellow background (in Tab. 1 ENG) are found in Sets in the first combination as well as in the Sets in the second combination. The numbers with the red background represent other options of numbers found in Sets that both combinations are made of, but it is sufficient if only one number is common, such as 29 in the case of S5 (I combination) and S13 (II combination). Of course, the feature can and should also recognize 16 as a common number, but this is not absolutely essential for the final result, i.e. for the formation of combination pairs.

2. If a Set is to be used twice, e.g. in the first combination we would have: S1, S1, S3, S5, S10 (1,1, 3, 5, 10). In this situation, two numbers from the S1 set can be used to find identical numbers in the Sets of the II combination, e.g. the 5 in S2 and the 2 in S3. Conversely, if a set occurs only once in a combination, this means that at most only one number from the set is available. In the example, for instance, since P10 is contained only once in the I combination: "P1, P3, P5, P10", a maximum of only one number from: 11, 33 may be taken into account.

3. IMPORTANT! In point 1, I described that at least one number from each individual Set of the I combination must occur in a Set of the second combination. There is one exception to this. If a combination of Sets is created, then the other sets (which are not contained in the combination) are automatically not taken into account. These sets, that are not taken into account, also have numbers assigned to them. Since the Sets in both combinations are equipped with different numbers, but some (quite a few) of these numbers are the same, the only difference is that they are to be found in other Sets, it can happen that a number that occurs, for example, in the Sets of the I combination, does not occur at all, in all the of Sets, which the second combination could be made out of. I have also prepared a table for this. (attach. 2 Tab.2 ENG)

Hypothetically, if the number 18 did not exist in S3 of the I combination, then there would be no number at all in the S3 set that also occurs in the Sets from the second combination. According to the first point, one would therefore have to stamp the I combination as not suitable for pair formation. If, however, a number such as 3 or 8 (yellow background, Tab. 2) does not occur in any Set that is available to create the second combination, then the *first* combination may still be used to form a pair.

D1 and G1 are Sets which are also filled with numbers. These numbers shall be used in the further filtration of the pairs. So, for example, making a feature that would allow to only show pairs of combinations in which a number from D1 or G1 has been the number that allowed to form a pair. Another great feature would be to limit the amount of the identical sets used in both combinations forming pair, so when it is equal to max. 2 then for instance a pair like 1, 2, 3, 10 + 1, 2, 3, 14 would not be possible.

Combi Pairs.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Combinations (1st list)							S1	S2	S3	S4	S5	S6	S7	S8	S9	S10	S11	S12	S13	S14		D1	G1
2	S	1	2	5					2	15	1	4	8	28	7	17	23	11	20	12				1	3
3	S	1	2	9					5	19	3	14	9				25	33		31				4	6
4	S	1	3	10					13	26	18	27	16											12	14
5	S	1	4	5					22	35	21		29											21	23
6	S	1	5	10					24		32													23	25
7	S	2	3	10
8	S	2	4	8
9	S	2	4	10
10	S	2	6	8
11	S	2	6	10
12	S	2	6	12
13	S	2	7	12
14	S	3	5	12
15	S	3	6	9
16	S	3	6	12
17	S	3	7	12
18	S	4	5	12
19	S	4	7	10
20	S	5	6	9
21	S	1	2	6	9
22	S	1	2	7	10
23	S	1	2	8	11
24	S	1	3	4	10
25	S	1	3	5	10
26	S	1	3	6	10
27	S	1	3	7	10
1st combi

Combi Pairs.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Combinations (2nd list)							S1	S2	S3	S4	S5	S6	S7	S8	S9	S10	S11	S12	S13	S14		D1	G1
2	S	1	4	7					1	4	2	15	13	19	30	17	18	6	14	21	16	32		6	2
3	S	1	4	11					7	5	9	33	22	35					23		20			9	8
4	S	1	5	10					10	11	28		24								29			11	11
5	S	1	7	10					12	27	31													25	27
6	S	2	4	10					26
7	S	2	4	12
8	S	2	4	14
9	S	2	5	14
10	S	2	7	12
11	S	3	4	7
12	S	3	4	9
13	S	3	4	11
14	S	3	5	12
15	S	3	7	12
16	S	4	5	12
17	S	4	7	10
18	S	4	7	14
19	S	5	6	9
20	S	5	6	11
21	S	5	6	14
22	S	5	7	14
23	S	6	7	12
24	S	6	7	14
25	S	1	2	4	7
26	S	1	2	4	9
27	S	1	2	4	11
28	S	1	2	5	10
29	S	1	2	7	10
30	S	1	2	10	13
31	S	1	3	4	7
32	S	1	3	4	10
33	S	1	3	7	10
34	S	1	3	10	11
35	S	1	3	10	13
36	S	1	4	6	7
37	S	1	4	6	11
38	S	1	4	7	9
39	S	1	4	7	10
40	S	1	4	7	13
41	S	1	4	8	11
42	S	1	4	9	11
43	S	1	4	9	13
44	S	1	4	10	11
45	S	1	4	10	13
46	S	1	4	12	13
47	S	1	5	6	9
48	S	1	5	6	10
49	S	1	5	8	10
50	S	1	5	8	11
51	S	1	5	9	10
52	S	1	5	10	11
53	S	1	6	7	10
54	S	1	6	7	11
55	S	1	6	7	13
56	S	1	6	9	11
57	S	1	6	10	11
58	S	1	6	10	13
59	S	1	6	12	13
60	S	1	7	8	10
61	S	1	7	8	11
62	S	1	7	9	10
63	S	1	7	12	13
64	S	2	3	9	12
65	S	2	3	9	13
66	S	2	3	9	14
67	S	2	3	10	12
2nd combi

Combi Pairs.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	combination from 1st list						+	combination from 2nd list
2							+
3							+
4							+
5							+
6							+
7							+
8							+
9							+
10							+
11							+
12							+
13							+
14							+
15							+
16
17
18
19
20
21
Pairs

 

[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!

Cross posted at: Need to create pairs of combinations
If you have posted the question at more places, please provide links to those as well.

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

 

[bansche123]

Cross posted at:

Need to create pairs of combinations [SOLVED]

Dear Forum Users, In the file Combi Pairs there are three worksheets. The first is titled 1st combi. In this worksheet you can find combinations in columns ranging from B to G. The combinations consist of sets numbered from 1 to 14. S1 means the first set, S2 the second and so on. So...

www.excelforum.com

 

[bansche123]

Unfortunately, I have only now noticed that I have forgotten to describe a detail in the paragraph that occurs after the Tab. 2 ENG. I have now added the information in the paragraph. To make it more recognizable I have written it with large letters.

Hypothetically, if the number 18 did not exist in S3 of the I combination AND IN THE SECOND COMBINATION, SET S9 WOULD NOT BE PRESENT, then there would be no number at all in the S3 set that also occurs in the Sets from the second combination. According to the first point, one would therefore have to stamp the I combination as not suitable for pair formation. If, however, a number such as 3 or 8 (yellow background, Tab. 2) does not occur in any Set that is available to create the second combination, then the *first* combination may still be used to form a pair.

 

[Marc L]

Hi,​

​

unfortunately you also forgot to attach the expected result ! …​

​

 

[bansche123]

Hi,​

​

unfortunately you also forgot to attach the expected result ! …​

​

Well, I have (in detail) described how the result should look like + I gave an example of a result, so a pair of combinations that fits the requirements, namely the pair: 1, 3, 5, 10 + 2, 3, 9, 13

It's impossible for me to give every single result, because my brain would explode trying to find all possible pairs of combinations. That is exactly why I reached out to the forum.

 

[Marc L]

​

So hope someone could decypher your need …​

 

[bansche123]

​

So hope someone could decypher your need …​

Please tell me what exactly isn't understandable, so I can try to explain it again.

 

[Marc L]

​

Another point : according to Excel what kind of 'feature' are you expecting for ?​

 

[bansche123]

​

Another point : according to Excel what kind of 'feature' are you expecting for ?​

TBH whatever will simply work. If it's possible to do with formulas, then why not, the same goes for a macro.