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]

Peter......This is *exactly* what I want. Thank you for creating this for me and I hope it may help others or perhaps it could be slightly modified to suit their requirements. For example it could easily incorporate 'names' instead of 'numbers'.

Peter Perfect was here.................

Also thanks to everybody else for your valuable input.

MaxTrax

 

[Peter_SSs]

Peter......This is *exactly* what I want. Thank you for creating this for me ..

Cheers, glad it helped.

For example it could easily incorporate 'names' instead of 'numbers'.

I did mean to allow for that sort of thing, but eventually forgot before posting.

So, for the record, to allow for pairs where the items in the pair might already contain spaces, use a colon (or some other character that will not appear in the data) and modify the following line like this:

If IsEmpty(origprs) Then origprs = Split("/Name 1/Name 2/:/Name 1/Name 3/:/Name 1/Name 4/:/Name 2/Name 3/:/Name 2/Name 5/:" _
                                    & "/Name 3/Name 4/:/Name 3/Name 6/:/Name 4/Name 7/:/Name 5/Name 6/:/Name 5/Name 8/:/Name 6/Name 7/:" _
                                    & "/Name 6/Name 9/:/Name 7/Name 10/:/Name 8/Name 9/", ":")

Excel Workbook
	B
1	Name 1/Name 4, Name 6/Name 9, Name 2/Name 5
2	Name 3/Name 6, Name 1/Name 4, Name 7/Name 10, Name 8/Name 9
3	Name 3/Name 4, Name 1/Name 2
Sheet2

 

[MaxTrax]

Peter
I already have use for the 'Names' version so again, great work and much thanks.

2Y'sUR2Y'sUBUNoURT2Y's4Me................

MaxTrax

 

[MaxTrax]

Sorry LockeGarmin. I only just saw your UDF.

Also excellent code. I'm now equipped with something I've needed for a long time. (In Excel I mean )

I won't be using this with a million rows more like 3 or 4 K

Thanks again guys.

MaxTrax

 

[Peter_SSs]

2Y'sUR2Y'sUBUNoURT2Y's4Me.

 

[MaxTrax]

Hello Guys,

I’m back again. Now looking for 2 formulas (or udf's) for a match, or that should be a partial match, and using them with the IF statement (or whatever functions etc are applicable) to give result of "Y" if true or "" if false in column C at C1 (For is 1st formula)

For example I am trying to *exactly* match (or not match) B1 to anywhere in A1

B1 (20) fails because of 200 in A1 string, B2 (2) fails because of many 2's in A2 string
B3 (10) fails because of 100 & 110 in A3 string and so on.

Also I would like another similar formula but this time for a range.

*Exact* match for B10 to anywhere in A1:A10 and again give result of "Y" if true or "" if false in column C at C10
B10 may appear numerous times but that is ok.

This, of course, won't work for 1st formula

=IF(MATCH("*" & B1 & "*", A1, 0),"Y","")

	A	B	C	D	F
1	190/200, 60/90, 310/340, 140/150, 5/30, 280/290, 100/110, 40/70, 210/240	20	IF(MATCH(then result if true "Y", if not true ""
2	110/140, 260/270, 230/240, 290/300, 10/40, 160/170, 190/200, 20/30, 50/60	2
3	130/140, 20/50, 270/300, 250/280, 260/290, 110/120, 320/350, 30/60, 70/100	10
4	220/250, 210/240, 20/50, 310/320, 300/330, 140/170, 70/80, 260/270, 5/10
5	5/10, 40/70, 220/250, 230/260, 280/290, 100/130, 330/360, 20/30, 270/300
6	110/140, 230/260, 180/210, 70/100, 270/300, 320/330, 50/60, 190/200, 5/20
7	220/230, 70/80, 170/200, 10/40, 30/60, 100/130, 160/190, 290/300, 250/260
8	90/120, 200/210, 220/250, 10/20, 100/130, 230/240, 80/110, 30/60, 310/340
9	280/310, 330/360, 250/260, 140/170, 200/210, 290/320, 40/70, 80/90, 240/270
10	20/50, 280/290, 70/80, 150/180, 250/260, 90/120, 160/190, 270/300, 170/200	270	IF(MATCH(Range then result if true "Y", if not true ""

<tbody>
</tbody>

Thanks for any help

MaxTrax

 

[MaxTrax]

And I need a 3rd formula please Guys,

*Exact* match for *any* number in range B1:B10 to A1 and again give result of "Y" if true or "" if false in column C at C10
B1:B10 numbers may repeat but that is ok.

	A	B	C	D	F
1	190/200, 60/90, 310/340, 140/150, 5/30, 280/290, 100/110, 40/70, 210/240	40
2		200
3		130
4		30
5		170
6		310
7		120
8		90
9		5
10		270	IF(MATCH(then result if true "Y", if not true ""

<tbody>
</tbody>

Thanks

 

[Peter_SSs]

You would have been better to start a new thread for these questions as they are quite different. There could be helpers who can answer these questions but wouldn't look this far through the thread if the thread title and/or the first page did not look like something they could, or wanted to, answer.

However, now you have started, probably best to stay here.

See if these, copied down, do what you want. If not please explain why.

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

 

[MaxTrax]

You would have been better to start a new thread for these questions as they are quite different

Yes I did realize they are quite different and I was in two minds whether to start a new thread or not. Sorry about that. I'll now excuse myself to the outside doghouse

Anyway your answers (3) are, once again, exactly what I was seeking.

Thank you

MaxTrax

 

[Peter_SSs]

Anyway your answers (3) are, once again, exactly what I was seeking.

That's the good news.

The bad news is there is an error in the D1 formula.
It does not correctly return a "Y" if the only match to the column B value is right at the beginning and/or right at the end of the column A string.

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