Give random unique set of 9 pairs

M

MaxTrax

Board Regular
Joined
Nov 18, 2014
Messages
91
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. :biggrin:

MaxTrax
 
Sort by date Sort by votes
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
Click to expand...

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

MaxTrax
 
Upvote 0
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



[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD]190/200, 60/90, 310/340, 140/150, 5/30, 280/290, 100/110, 40/70, 210/240[/TD]
[TD="align: center"] 190 [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]60[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]310[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]240[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]90[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]150[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 
Upvote 0
MaxTrax said:
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
Click to expand...
Try

=IF(SUMPRODUCT(--ISNUMBER(FIND("/"&C1:C10&"/","/"&SUBSTITUTE(B1,", ","/")&"/")))=ROWS(C1:C10),"Y","N")
 
Upvote 0
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.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]217[/TD]
[TD="align: center"]218[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]645[/TD]
[TD="align: center"]509[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]651[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]217[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]756[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]509[/TD]
[TD="align: center"]Result "Y"[/TD]
[/TR]
</tbody>[/TABLE]

Code:
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







​
 
Last edited:
Upvote 0
No takers for the UDF problem? I have a solution for the normal formula.

MaxTrax
 
Last edited:
Upvote 0
MaxTrax said:
No takers for the UDF problem? I have a solution for the normal formula.

MaxTrax
Click to expand...
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')
 
Upvote 0
Hi Peter,

Sorry for the delayed answer. Been busy.

What is your standard formula?
Click to expand...

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


Change 'xlPart' to 'xlWhole’
Click to expand...


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


Back to udf
Code: 
=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


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

[TABLE="width: 466"]
<tbody>[TR]
[TD="width: 466"]Udf Pairs(9) gives A1
[TABLE="width: 466"]
<tbody>[TR]
[TD="width: 466"]**Note A2 just showing A1 sorted**
[TABLE="width: 466"]
<tbody>[TR]
[TD="width: 466"]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.[/TD]
[/TR]
[TR]
[TD="width: 466"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Pairs(9) gives 5/10, 20/30, 40/50, 100/130, 150/180, 160/170, 240/270, 280/310, 330/360[/TD]
[/TR]
</tbody>[/TABLE]
New Udf gives 60/90, 70/80, 90/120, 110/140, 190/200, 220/230, 250/260, 290/300, 320/350


Thanks
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
338
Spaceballs_the_fanboy
S
M
  • Question Question
VBA code failing and miscalculating when running high numbers of lines (200+) but ok when doing it line by line
Replies
3
Views
118
DanteAmor
DanteAmor
V
  • Question Question
Copy and Paste Multiple sheet data in one sheet Formula
Replies
7
Views
538
vishu
V
F
  • Solved
Ramp Up formula without VBA
Replies
9
Views
431
Fluff
Fluff
S
  • Question Question
Pro Rata a Sumproduct Calculation
Replies
1
Views
354
myall_blues
myall_blues

Forum statistics

Threads
1,226,834
Messages
6,193,214
Members
453,779
Latest member
C_Rules

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top