Unique groups of two numbers, all numbers in a unique group

[dtaylor646]

Hello everyone,

Though some other posts have had similar solutions, I want to pair a list of unique identifiers to others with each pairing unique for each month. April has #1 with #3 and necessarily #3 with #1 The first three pairings were manual and would like a formula to generate the other pairings for other months, where the pairings do not repeat from the previous months' pairs. Thank you for any and all help!

Identifier	April	May	June	July
1	3	2	4	[formula here]
2	4	1	3
3	1	4	2
4	2	3	1
5	7	6	8
6	8	5	14
7	5	8	20
8	6	7	5
9	11	10	12
10	12	9	11
11	9	12	10
12	10	11	9
13	19	14	15
14	20	13	6
15	17	16	13
16	18	15	17
17	15	18	16
18	16	17	19
19	13	20	18
20	14	19	7

<tbody>
</tbody>

 

[Eric W]

The formula vs. macro logic seems similar. Make a set of possible values, randomly choose one, then repeat for the next row. If you run out of possibilities, try again from the top. For a formula, that's F9, for my macro I loop, up to 1000 times. I was a little surprised at how few times I needed to loop, even when looking at 19 columns.

Incidentally, nice formula!

 

[dtaylor646]

Hello Marcelo, It looks like the formula is working as I individually add columns to the right. If I do not paste-value the new column before adding another, the numbers will regenerate and create new pairs of combinations. I mention this not as a criticism, but for anyone who may use the formula above for their own paired-grouping in the future.

Eric, I am new to VBA and look forward to trying your solution as well to continue to grow in that dimension. I love that in Excel there is more than one solution! Thank you all!! -Dan