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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June [/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][formula here][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]11[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]19[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20[/TD]
[TD]13[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]18[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]13[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]14[/TD]
[TD]19[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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