Give random unique set of 9 pairs

[MaxTrax]

Hi People,

I have a set of 58 pairs (I’m using paired numbers here but they could be anything).

I am hoping someone can help in providing a VBA macro or formula to complete the following:

The result should give a unique random set of 9 pairs in any order with of course no repeats of pairs.
Eg 1: This result would be correct. 5/10, 20/30, 110/140, 70/ 80, 150/180, 260/270, 40/50, 200/230, 190/220 (A set of 9 unique pairs)

Eg 2: This result would be incorrect. 30/60, 40/50, 40/70 (as both contain 40), 140/170, 5/20, 5/30 (as both contain 5), 100/110, 20/30, 250/280 (Not a set of 9 unique pairs)

The correct formula will be copied over many cells to give a new random set of 9 in each. It is quite possible some *new sets of 9* in different cells would repeat and that is ok.

Here are the 58 pairs.
5/10, 5/20, 5/30, 10/20, 10/40, 20/30, 20/50, 30/60, 40/50, 40/70, 50/60, 50/80, 60/90, 70/80, 70/100, 80/90, 80/110, 90/120, 100/110, 100/130, 110/120, 110/140, 120/150, 130/140, 130/160, 140/150, 140/170, 150/180, 160/170, 160/190, 170/180, 170/200, 180/210, 190/200, 190/220, 200/210, 200/230, 210/240, 220/230, 220/250, 230/240, 230/260, 240/270, 250/260, 250/280, 260/270, 260/290, 270/300, 280/290, 280/310, 290/300, 290/320, 300/330, 310/320, 310/340, 320/330, 320/350, 330/360

Thanks to anybody who helps.

MaxTrax

 

[MaxTrax]

Correction:

=IF(SUMPRODUCT(--ISNUMBER(FIND("/"&B1&"/","/"&SUBSTITUTE(A$1:A$10,", ","/")&"/"))),"Y","")


BTW, If you are using these formulas with text rather than numbers AND you want "Fred" to match "FRED" then change all the FIND functions to SEARCH instead

Got it.....Thank you Sir. I'll now continue construction

MaxTrax

 

[MaxTrax]

Peter, If you can could you respond to this?

http://www.mrexcel.com/forum/excel-...umbers-[4-quads-plus-1-pair].html#post4509086

Thanks

 

[MaxTrax]

Hi,

Could I also have a formula for a result of "Y" in D10 if ALL the numbers in C1:C10 (there can be repeats in that range) match ANY of the numbers in B1

A	B	C	D
1	190/200, 60/90, 310/340, 140/150, 5/30, 280/290, 100/110, 40/70, 210/240	190
2		60
3		310
4		40
5		40
6		240
7		90
8		150
9		30
10		5	Y
11

<tbody>
</tbody>

Thanks

 

[Peter_SSs]

Could I also have a formula for a result of "Y" in D10 if ALL the numbers in C1:C10 (there can be repeats in that range) match ANY of the numbers in B1

Try

=IF(SUMPRODUCT(--ISNUMBER(FIND("/"&C1:C10&"/","/"&SUBSTITUTE(B1,", ","/")&"/")))=ROWS(C1:C10),"Y","N")

 

[MaxTrax]

Exactly right.

Again, Thank you


MaxTrax

 

[MaxTrax]

Can the below UDF be modified (corrected) to give an *exact* (not part of, as in 100 & 1000) match of ANY cell in a range to ANY cell in another range? There can be repeats in both ranges. There can be more than one match and that is ok.

Match any cell in B1:B9 to any cell in A1:A9 and give result at C9 or wherever.

B3 & B9 repeat and is ok. A2 and B5 match so result "Y"

E.g.

	A	B	C
1	110	127
2	217	218
3	645	509
4	250	651
5	80	217
6	71	45
7	800	76
8	250	756
9	98	509	Result "Y"

<tbody>
</tbody>

Code:

Public Function AnyMatch(r1 As Range, r2 As Range) As String
    Dim v As Variant, r As Range, rTemp As Range
    AnyMatch = "NO"


    For Each r In r1
        v = r.Value
        Set rTemp = r2.Find(What:=v, After:=r2(1), LookAt:=xlPart)
        If rTemp Is Nothing Then
        Else
            AnyMatch = "YES"
            Exit Function
        End If
    Next r
End Function

I would also appreciate a normal formula for above if possible.

Thanks

MaxTrax

​

 

[MaxTrax]

No takers for the UDF problem? I have a solution for the normal formula.

MaxTrax

 

[Peter_SSs]

No takers for the UDF problem? I have a solution for the normal formula.

MaxTrax

If you can do it with a standard formula, why bother with the complication (& often reduced efficiency) of a udf?
What is your standard formula?

(FWIW a very simple change to the udf would do it. Change 'xlPart' to 'xlWhole')

 

[MaxTrax]

Hi Peter,

Sorry for the delayed answer. Been busy.

What is your standard formula?

=IF(SUMPRODUCT(COUNTIF(A1:A9,B1:B9))>0,"Yes","No")

Change 'xlPart' to 'xlWhole’

Yes thanks. I was advised of this from somewhere else.


Back to udf

=Pairs(9)

Q1. Could the code be modified to give the result in ascending order by each leading number?
Result now: 300/330, 10/20, 140/170, 240/270, 310/320, 110/120, 40/70, 60/90, 250/260
Result required: 10/20, 40/70, 60/90, 110/120, 140/170, 240/270, 250/260, 300/330,310/320
Q2: Hard for me to structure this question. Sorry. Could a new udf be modified in/added to or separate from Pairs(9) – (I’m not sure how, if at all, this is done) to give a result of 9 unique pairs AND unique from what Pairs(9) gave AND the result in ascending order by each leading number? It obviously would have to run within the same boundaries as Pairs(9).
Please see example

	A	B	C	D
1	300/330, 10/20, 140/170, 240/270, 310/320, 110/120, 40/70, 60/90, 250/260 <tbody> </tbody>	40		5/30, 50/80, 100/130, 150/180, 160/190, 200/210, 220/230, 280/290, 350/360 <tbody> </tbody>
2	[10/20, 40/70, 60/90, 110/120, 140/170, 240/270, 250/260, 300/330,310/320] <tbody> </tbody>	330
3		20
4		260
5		20
6		170
7		240
8		70
9		110
10		250	Yes

<tbody>
</tbody>

Udf Pairs(9) gives A1 **Note A2 just showing A1 sorted** So if c10 = "Yes" as EVERY value in B1:B10 matches ANY value in A1 Then the NEW UDF gives a unique set of 9 pairs in D1 (that is compared to A1) Another E.g. <tbody> </tbody> <tbody> </tbody> Pairs(9) gives 5/10, 20/30, 40/50, 100/130, 150/180, 160/170, 240/270, 280/310, 330/360

<tbody>
</tbody>

New Udf gives 60/90, 70/80, 90/120, 110/140, 190/200, 220/230, 250/260, 290/300, 320/350


Thanks

 

[MaxTrax]

I have just realized that it’s not always possible to have 9 other unique pairs which are unique to what was given by Pairs(9).
So if 1 or 2 (1 only I think) have to contain one number from the Pairs(9) result that is fine.

Thanks