Sort groups of 4 numbers

[MaxTrax]

Hi,

Asking for a formula to do this please.
Sort this: (Unique quads) in A1
115/125/140/145, 20/25/40/50, 105/110/120/135, 55/60/70/80, 5/10/15/30, 75/85/90/95,

To this (by the smallest leading number) in A2
5/10/15/30, 20/25/40/50, 55/60/70/80, 75/85/90/95, 105/110/120/135, 115/125/140/145

Thanks

 

[Rick Rothstein]

Thank you Rick. Now I'm spoiled. There is a slight prob though.....

Pairs(9)

5/10, 40/70, 60/9, 80/110, 130/160, 140/170, 190/200, 250/280, 260/270 Only for the 60/90 otherwise all good. Thank you. I await the quads (said very sheepishly)

<tbody>
</tbody>

I missed putting in a slash delimiter after the 90. Here is the corrected code...

[table="width: 500"]
[tr]
	[td]Function Pairs(HowMany As Long) As String
  
  Dim X As Long, Z As Long, P As Variant, OrigPrs As Variant, Nums As Variant, Temp1 As Variant, Temp2 As Variant
  
  Application.Volatile
  Randomize
  OrigPrs = Split("/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[B][COLOR="#FF0000"][SIZE=2]/[/SIZE][/COLOR][/B] /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/")
  
  ReDim P(1 To HowMany)
  For X = 1 To HowMany
    P(X) = OrigPrs(Int((UBound(OrigPrs) + 1) * Rnd))
    Nums = Split(Mid(P(X), 2, Len(P(X)) - 2), "/")
    P(X) = Join(Nums, "/")
    For Z = 0 To UBound(Nums)
      OrigPrs = Filter(OrigPrs, "/" & Nums(Z) & "/", False)
    Next
  Next
  
  For X = 1 To UBound(P)
    For Z = X To UBound(P)
      If Val(P(Z)) < Val(P(X)) Then
        Temp1 = P(X)
        Temp2 = P(Z)
        P(X) = Temp2
        P(Z) = Temp1
      End If
    Next
  Next
  
  Pairs = Join(P, ", ")
  
End Function[/td]
[/tr]
[/table]

 

[Rick Rothstein]

I reread the thread and got the impression that you will have multiple group-sets to process. If so, I thought I would help you out so that you don't have to surround each number with a slash and cover the Pairs and Quads (even Triplets, Quints and higher) all with the same piece of code. First, to simplify your set up... instead of putting a group-set inside the code with each number surrounded by a slash (a real pain to setup), just put the Pairs or Quads in a cell as a normal comma delimited text string... the code will handle formatting it correctly for use by the code. So, for your Pairs example, put this in a cell (say A1)...

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, 330/360

and for your Quads example, put this in a cell (say B1)...

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

Then, you can pass each of those cells into the UDF as the first argument and, as the second argument, specify how many sets you want returned to you. So, if you wanted 5 Pairs, you would put this formula in a cell...

=UniqueGroups(A1,5)

and if you wanted 3 Quads, you would use this formula in a different cell...

=UniqueGroups(B1,3)

In otherwords, the code is smart enough to figure out if you have Pairs, Quads (Triplets, Quints or whatever). Remember, all you have to put in the cell is a normal, comma delimited text string (no extra slashes required). Here is the code that does this...

[table="width: 500"]
[tr]
	[td]Function UniqueGroups(S As String, HowMany) As String
  
  Dim X As Long, Z As Long, P As Variant, OrigQuads As Variant, Nums As Variant, Temp1 As Variant, Temp2 As Variant
  
  Application.Volatile
  Randomize
  OrigQuads = Split("/" & Replace(Replace(S, " ", ""), ",", "/ /") & "/")
  
  ReDim P(1 To HowMany)
  For X = 1 To HowMany
    P(X) = OrigQuads(Int((UBound(OrigQuads) + 1) * Rnd))
    Nums = Split(Mid(P(X), 2, Len(P(X)) - 2), "/")
    P(X) = Join(Nums, "/")
    For Z = 0 To UBound(Nums)
      OrigQuads = Filter(OrigQuads, "/" & Nums(Z) & "/", False)
    Next
  Next
  
  For X = 1 To UBound(P)
    For Z = X To UBound(P)
      If Val(P(Z)) < Val(P(X)) Then
        Temp1 = P(X)
        Temp2 = P(Z)
        P(X) = Temp2
        P(Z) = Temp1
      End If
    Next
  Next
  
  UniqueGroups = Join(P, ", ")
  
End Function[/td]
[/tr]
[/table]

 

[MaxTrax]

Here is the code that does this...

Well Rick I'm overwhelmed.....That truly is professional.


It worked on everything I tried. Fantastic.


This code is all I need and answers all my needs except...... Guys I'm assembling another related (definitely related ) pairs question which I will post as soon as I have finished it.


Actually I'll wait to see if you require more info because.....

I posted this question (2nd question only) Post #29 on May 19th but received no usable response.

Here:
http://www.mrexcel.com/forum/excel-questions/936216-give-random-unique-set-9-pairs-3.html


Hopefully I'm in order asking this here.....

 

[MaxTrax]

Now that the Pairs, Quads UDF has been finished I am seeking another UDF which I will use for the purpose outlined in the below table. It relates to 9 pairs only. It should give an equal number of 9 unique (but different) and sorted pairs or in some cases an exception of 8 unique pairs plus any other non-unique pair.

These will be ALL different from the 9 pairs given in A1. (except for the exception above)

The reason for the 8+1 exception is that there are, sometimes, only 8 different unique pairs than compared to those in A1. This 1 exception will have 1 of the numbers in A1 and that's ok.


Example:

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

<tbody>
</tbody>

UDF Pairs(9) or UDF UniqueGroups gives A1
B1:B10 are random numbers between 5 and 360 (They can repeat)
C10 formula gives 'Match' if EVERY value in B1:B10 is matched ANYWHERE in A1
(In this case that is true so we get "Match")
Formula is:

IF(SUMPRODUCT(--ISNUMBER(FIND("/"&A1:A10&"/","/"&SUBSTITUTE(A1,", ","/")&"/")))=ROWS(A1:A10),"Match","-")

The UDF I'm wanting now will only be used if "Match" is given at C10

It will give the result in D1. Something like this....
In D1, =IF(C10="Match",DiffPairs(9),"") and gives for example.....
5/30, 50/80, 100/130, 150/180, 160/190, 200/210, 220/230, 280/290, 350/360


I hope this is possible.

This is definitely the last request!! (I'm offline for 8 hrs - Yes I can hear the cheers )

 

[Rick Rothstein]

I am seeking another UDF which I will use for the purpose outlined in the below table. It relates to 9 pairs only. It should give an equal number of 9 unique (but different) and sorted pairs or in some cases an exception of 8 unique pairs plus any other non-unique pair.

These will be ALL different from the 9 pairs given in A1. (except for the exception above)

The reason for the 8+1 exception is that there are, sometimes, only 8 different unique pairs than compared to those in A1. This 1 exception will have 1 of the numbers in A1 and that's ok.

You may have to rethink this. If your set of groups is not sufficiently large enough (I don't know of any criteria to determine if a given set is sufficient large enough though), you may not be able to find even an additional 8 unique sets for Column C. Your question, as stated, boils down to finding 17 or 18 unique sets, the first 9 of which go in Column A and the remainder go in Column C. Due to the random selection process and how other pairs with the same numbers get eliminated, I found instances using this set of 57 pairs you posted originally...

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, 330/360

where only a total of 14 unique pairs could be found (in other words, asking UniqueGroups to form 15 pairs failed with a #VALUE! error. So, will the group of total pairs always be guaranteed to be large enough to produce at least 17 unique pairs? If not, what should be done?

 

[MaxTrax]

Hi Rick,

There are 58 pairs (See below) and remember I’m only going to use this UDF with these 58 pairs, nothing else.

From my testing I always get 9 or 8 different pairs than those in A1. I’d be interested to see a 7 but if it exists then just use two pairs as per the examples below for the 8^th & 9^th in D1.

I also should have stated that if D1 is given then only the pairs contained there will be used.

These examples are real.

Example of where only 8 different pairs are possible in D1 so then add the extra 1 as set out:

9 in A1: 10/40, 20/50, 30/60, 70/100, 110/120, 130/160, 150/180, 170/200, 230/240 = 18

Then in D1 (if Match given)

8 different +1 in D1: 80/90, 190/220, 250/280, 260/290, 270/300, 310/340, 320/350, 330/360, ?/? = 16+2

So the numbers not ‘seen’ in the two sets (A1&D1) are 5, 140, 210 so any one of them could be used with one of their partners as setout below. Those ‘partners’ will already be selected in A1 and that is ok.

So for 5: 5/30 or 5/20 or 5/10 could be the 9^th in D1
For 140: 110/140 or 130/140 or 140/150 or 140/170 could be the 9^th in D1
For 210: 180/210 or 210/240 could be the 9^th in D1


Another example of where only 8 different pairs are possible in D1 so then add the extra 1:

9 in A1: 30/60, 40/70, 80/110, 160/170, 200/210, 220/230, 240/270, 280/290, 320/330 =18

Then in D1 (if Match given)

8 different+1 in D1: 5/10, 20/50, 90/120, 130/140, 150/180, 250/260, 310/340, 350/360, ?/? =16+2

Missing are 100, 190, 300 so for No9 (in D1) any of these with their partners for the 9^th pair is fine
For 100: 70/100, 100/110, 100/130
For 190: 160/190, 190/200, 190/220
For300: 270/300, 300/330


Of course sometimes 9 different pairs are possible in D1


Rick I’d be interested where you found only the 7 or was it because 57 verses 58 pairs


Here are the 58 pairs. 320/330 missing from your list
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

remainder go in Column C

I think you mean D. Yes?


Thanks

 

[MaxTrax]

Rick I’d be interested where you found only the 7

Correct Rick, I now see there could only be 7 pairs BUT that's fine just use any 2 pairs of the other numbers not selected with their partners.


BUT......
how do you know those other numbers until B1 pairs are actually selected? Is this impossible?
Or is there a way (mathematically) around it. It's easy manually.


Applies to 8 as well I guess.

(7 E.g)
A1 5/20, 40/70, 110/120, 130/140, 170/200, 180/210, 220/250, 270/300,
320/350
B1 30/60, 80/90, 160/190, 230/240, 260/290, 310/340, 330/360, ??/??, ??/??

 

[MaxTrax]

Bit more info....

(7 E.g)
A1 5/20, 40/70, 110/120, 130/140, 170/200, 180/210, 220/250, 270/300,
320/350
B1 30/60, 80/90, 160/190, 230/240, 260/290, 310/340, 330/360, ??/??, ??/??

When 7 are different from A1 and then can't choose another different 3 then this below.

Using above pairs:
The missing/unselected numbers here are 10, 50, 100, 150 & 280
So ANY 2, but only 1 from each, from of the following pairs for the other 2 pairs in D1 is ok.
E.g. 10/20, 100/110 is ok. This is not ok: 20/50, 50/60 (both have 50)

*NOTE* I didn't stress those last 2 lines in the 2 previous posts. I should have.


10: 5/10, 10/20, 10/40
50: 20/50, 40/50, 50/60, 50/80
100: 70/100, 100/110, 100/130
150: 120/150, 140/150, 150/180
280: 250/280, 280/290, 280/310

 

[MaxTrax]

If not, what should be done?

Hi Rick or anybody else,
Are you still interested in helping with this as I’ve had no reply for several days?
I understand people are very busy and can give only limited time to these forums.

where only a total of 14 unique pairs could be found

I have checked through the week and found that the maximum number of unique pairs that can be found can be as low as 13 (very rare but certainly possible)
An example: 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.
Another: 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

If anybody can help, either with a new UDF or maybe adjust/modify or add to, the UDF’s Pairs() or UniqueGroups to give the other 9 pairs as I set out and explained (not very well) in Posts #46, 47 & 48 above.
Obviously if 14, 15, 16 or 17 is the maximums then, for those 9 sets given in A1, the other 9 sets would be made up as:
For 13: 4 different & unique pairs plus 5 other non-different, non-unique pairs.
For 14: 5 different & unique pairs plus 4 other non-different, non-unique pairs.
For 15: 6 different & unique pairs plus 3 other non-different, non-unique pairs.
For 16: 7 different & unique pairs plus 2 other non-different, non-unique pairs.
For 17: 8 different & unique pairs plus 1 other non-different, non-unique pairs.

The ‘other non-different, non-unique pairs‘ will have to be formed from the unused numbers and their partners as I explained in posts #46, 47 & 48 for 16 & 17 and applied for 13, 14 & 15 as well.

I realize this is quite complex (for me anyway) and may not be able to be achieved. If it can’t please tell me.

I declare that I have asked this on another forum this week but have not received a solution.

Give the other numbers
Post #7


Thanks