Give the other numbers

…this would cause a reduction in the unused number pair sets to:
120---90/120, 110/120, 120/150
130---100/130, 130/140, 130/160
240---210/240, 230/240, 240/270
250---220/250, 250/260, 250/280
300---270/300, 290/300, 300/330
310---280/310, 310/320, 310/340
360--- 330/360,350/360

When selecting 2 more pairs would it be OK to choose the 2 blue highlighted pairs (notice they both have 330 in)?
Or the two red highlighted pairs which both have 280 in?

However, after adding the "2 more pairs 340/350 & 350/360"

Click to expand...

Yes, that is correct.

I should have only given these 2 sets as examples of where there are only 13 unique pairs available in total. I shouldn’t have given that result for udf Others() as I just entered it manually therefor it was wrong.
5/10, 20/30, 40/50, 80/90, 100/110, 140/150, 160/170, 200/210, 220/230, 260/270, 280/290, 320/330, 340/350
And
5/30, 10/20, 50/60, 70/80, 110/120, 130/140, 170/180, 190/200, 230/240, 250/260, 290/300, 310/320, 350/360

When selecting 2 more pairs would it be OK to choose..........

Click to expand...

No
Only these: (And only 1 from each)
120---90/120, 110/120, 120/150 then only 120/150
130---100/130, 130/140, 130/160 then either 130/140 or 130/160
240---210/240, 230/240, 240/270 then either 230/240 or 240/270
250---220/250, 250/260, 250/280 then either 220/250 or 250/260, 250/280 << see below
300---270/300, 290/300, 300/330 then either 270/300 or 290/300, 300/330 << see below
310---280/310, 310/320, 310/340 then either 280/310 or 310/320 or 310/340
360--- 330/360,350/360 then either 330/360 or 350/360
Because:
Pairs(9) result 5/10, 40/50, 140/150, 160/170, 220/230, 260/270, 280/290, 320/330, 340/350
Others() result 20/30, 60/90, 70/80, 100/110, 180/210, 190/200
120--- 90/120, 110/120 are used in Others() result (90,110)
130--- 100/130 is used in Others() result (100)
240--- 210/240 is used in Others() result (210)
250--- All ok but if 250/280 is used then 280/310 should not be selected (very hard )
300--- All ok but if 300/330 is used then 330/360 should not be selected
310--- All ok but if 280/310 is used then 250/280 should not be selected
360--- All ok but if 330/360 is used then 300/330 should not be selected

In other words, the non-unique pairs (3 in this case) can only have as one of their numbers an unused number and the other number will be one used in the udf Pairs(9) selection but can’t ‘double up’ with a Pairs(9) number as you pointed out.
Does that make sense?

These would be ok.
20/30, 60/90, 70/80, 100/110, 120/150, 130/140, 180/210, 190/200, 300/330
20/30, 60/90, 70/80, 100/110, 130/160, 180/210, 190/200, 240/270, 350/360
20/30, 60/90, 70/80, 100/110, 120/150, 180/210, 190/200, 280/310, 300/330
20/30, 60/90, 70/80, 100/110, 130/140, 180/210, 190/200, 250/280, 300/330
20/30, 60/90, 70/80, 100/110, 130/160, 180/210, 190/200, 250/280, 330/360

These would not be ok:
20/30, 60/90, 90/120, 70/80, 100/110, 180/210, 190/200, 210/240, 330/360
(90 & 210 are both used in Others() result)
20/30, 60/90, 70/80, 100/110, 130/140, 180/210, 190/200, 250/280, 280/310 << As you asked
(250 & 310 are unused but double up with 280 in Pairs(9) result so neither can’t be used)

Another Example:
Pairs(9) result: 5/20, 40/70, 80/90, 110/140, 120/150, 160/190, 250/280, 260/270, 290/320
Others() result: 30/60, 100/130, 170/180, 200/210, 220/230, 300/330, 310/340, 350/360, ??/??
Unused: 10,50,240
So the 9[SUP]th[/SUP] could be any 1 of these but not the exceptions.
For 10 these 5/10 or 10/20 or 10/40 All are ok as none are used in Others() result
For 50 these 40/50 or 20/50 or 50/80 but not 50/60 as 60 is used in Others() result
(Note here: Either 10/40 or 40/50 are ok as we are only choosing one more (9[SUP]th[/SUP])
For 240 this 240/270 but not 210/240 or 230/240 as 210 and 230 are used in Others() result

And Another Example:
Pairs(9) result: 10/40, 60/90, 70/100, 80/110, 140/150, 200/210, 220/250, 230/240, 310/340
Others() result: 5/20, 130/160, 170/180, 260/270, 280/290, 300/330, 320/350, ??/??, ??/??
Unused: 30, 50, 120, 190, 360
So the 8[SUP]th[/SUP] & 9[SUP]th[/SUP] could be any 2 but only 1 from each unused number of these but not the exceptions

For 30 use 30/60 but not 5/30 or 20/30 as 5 and 20 are used in Others() result
For 50 use 40/50 or 50/60 or 50/80 but not 20/50 as 20 is used in Others() result
For 120 use 90/120 or 110/120 or 120/150 as all are ok and not used in Others() result
For 190 use 190/200 or 190/220 but not 160/190 as 160 is used in Others() result
For 360 It can’t be used as 330/360 or 350/360 have 330 and 350 in Others() result

So these would be all ok for Others() result: (Bolds are the non-unique pairs)
5/20, 30/60, 130/160, 170/180, 190/120, 260/270, 280/290, 300/330, 320/350
5/20, 50/80, 130/160, 170/180, 190/220, 260/270, 280/290, 300/330, 320/350
5/20, 110/120, 130/160, 170/180, 190/200, 260/270, 280/290, 300/330, 320/350

These would not:
5/20, 40/50, 50/60, 130/160, 170/180, 260/270, 280/290, 300/330, 320/350
(40/50, 50/60 are both from the same unused number 50. Only 1 pair from an unused number can be chosen)
5/20, 30/60, 130/160, 160/190, 170/180, 260/270, 280/290, 300/330, 320/350
(As 160 has already been used in Others() result)
5/20, 130/160, 170/180, 190/200, 260/270, 280/290, 300/330, 320/350, 350/360
(As 360 can’t be used as explained above)

(250 & 310 are unused but double up with 280 in Pairs(9) result so neither can’t be used)

Click to expand...

Should read
(250 & 310 are unused but double up with 280 in Pairs(9) result so only one can be used)

One for you to test; see green cells in column A of sheet2 of the attached.

Really really close but (there’s always a but)………..

See ‘Sheet3’ and in particular Examples 1, 5, 6, & 7

Your code is quite remarkable.

It's bedtime here and I'd like you to check something:
In B57 of sheet3 of your last attachment you say 'These 9 should have been chosen' referring to:
40/50, 70/80, 90/120, 100/110, 130/160, 150/180, 190/220, 200/210, 310/340

Can you confirm that the following could also have been chosen, what's more: nothing else?

40/50 70/100 80/90 110/120 130/160 150/180 190/220 200/210 310/340
40/50 70/100 80/110 90/120 130/160 150/180 190/220 200/210 310/340
40/70 50/80 90/120 100/110 130/160 150/180 190/220 200/210 310/340

Can you confirm that the following could also have been chosen, what's more: nothing else?

Click to expand...

Yes, correct.

And another couple if it helps:
1).
Pairs(9) 5/10, 80/90, 120/150, 160/170, 210/240, 220/230, 260/290, 310/340, 350/360
Others() 20/30, 40/50, 70/100, 110/140, 190/200, 250/280, 270/300, 320/330 ¦ 60/90, 130/160, 150/180

Correct 20/30, 40/70, 50/60, 100/110, 130/140, 190/200, 250/280, 270/300, 320/330 ¦ unused 180
or
Correct 20/50, 30/60, 40/70, 100/130, 110/140, 190/200, 250/280, 270/300, 320/330 ¦ unused 180

2).
Pairs(9) 40/70, 80/90, 120/150, 130/140, 170/180, 190/220, 290/320, 310/340, 350/360
Others() ** 5/10, 20/30, 50/60, 100/110, 200/210, 230/240, 250/260, 270/300 ¦ 160/170, 280/310, 320/330

Correct 5/10, 20/30, 50/60, 100/110, 200/210, 230/240, 250/280, 260/270, 300/330 ¦ unused 160

** As you know there are other results but it’s only the non-uniques that change. It's only 250, 260, 270, 280, 300 & 330 that matter in this case. (2)

The attached is very much work-in-progress. You need to test it.
There's another version of Others(): Others2() in the dark green cells.
It uses recursion to find multiple possibilities and only keeps the ones with most pairs, then it takes one at random, then adds the non-uniques.
Recursion's not necessarily the fastest way but it sure cuts down on code lines.
Since it finds lots and lots of possibilities be very careful what cells you use it on - if there are only a few pairs in the cell that the function refers to, then it might take a very very long time to calculate, even just one cell. At the moment, in the attachment there are only 27 cells with the new function in and it takes a few seconds to recalculate that sheet.

re:

it is related to pairs of people for selection purposes in a unique administration organisation

Click to expand...

Tell us more (PM me if it's sensitive)

The attached is very much work-in-progress

Click to expand...

And believe me it’s far beyond me. Great achievement as the results validate it.

You need to test it

Click to expand...

See ‘Sheet4’ in attached file (color's a bit much but my eyes........)

I restricted the number of non-uniques to 3 during development (you gave the impression earlier (in a file) that you wanted some restriction). I haven't tested yet how long it might take the code to produce more when pairs(any number) is used.
You can change that restriction by changing the 3 in:
AddCount = Application.Min(UBound(nonuniques) + 1, 3)
to maybe 9.

Test again.

to maybe 9

Click to expand...

Yes that gave correct results for any Pairs() amount.

It errored out (#VALUE!), as it should, when Others2()code couldn’t find any other unique pairs.
E.g Sometimes in Pairs(14) or (16). Never => 12 of course. All good.

Does ‘Sub ggaarpp’ always need to be in its own module or could it be incorporated into the Pairs()/Others2() module? I’m just trying to, what’s the word, organize/cleanup the code a bit. That still didn’t sound right!!

Many thanks for ALL this p45cal. You really do have professional VBA skills.

FarCome said:

Yes that gave correct results for any Pairs() amount.

Click to expand...

Excellent.

FarCome said:

It errored out (#VALUE!), as it should, when Others2()code couldn’t find any other unique pairs.
E.g Sometimes in Pairs(14) or (16). Never => 12 of course. All good.

Click to expand...

Shouldn't some non-uniques be added then? Easy enough to implement.

FarCome said:

Does ‘Sub ggaarpp’ always need to be in its own module or could it be incorporated into the Pairs()/Others2() module? I’m just trying to, what’s the word, organize/cleanup the code a bit.

Click to expand...

As I said earlier: "The attached is very much work-in-progress". Everything can be in a single module.
What can be deleted altogether:
Function Others(myrng As Range)
Sub ggaarpp() (utility for development)
Function stillUnique(current, addition) As Boolean
and:
'Dim OrigPrs
Dim Destn As Range
at the top of module2

I'll tidy up and try to streamline/improve performance in the next few days.

Shouldn't some non-uniques be added then? Easy enough to implement.

Click to expand...

If you can that would be great.

I'll tidy up and try to streamline/improve performance in the next few days

Click to expand...

That also would be very helpful. Thanks