Sort groups of 4 numbers

M

MaxTrax

Board Regular
Joined
Nov 18, 2014
Messages
91
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
 
Sort by date Sort by votes
MaxTrax said:
Thank you Rick. Now I'm spoiled. There is a slight prob though.....

Pairs(9)

[TABLE="width: 445"]
<tbody>[TR]
[TD="width: 445"]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) :stickouttounge:[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
I missed putting in a slash delimiter after the 90. Here is the corrected code...
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]
 
Last edited:
Upvote 0
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
Click to expand...

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
Click to expand...

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...
Code: 
[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]
 
Upvote 0
Here is the code that does this...
Click to expand...

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.....
 
Upvote 0
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:

[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]10/20, 40/70, 60/90, 110/120, 140/170, 240/270, 250/260, 300/330, 310/320[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]5/30, 50/80, 100/130, 150/180, 160/190, 200/210, 220/230, 280/290, 350/360[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/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]Match[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
Code: 
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 :rolleyes:)
 
Last edited:
Upvote 0
MaxTrax said:
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.
Click to expand...
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
Click to expand...

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?
 
Upvote 0
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 8th & 9th 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 9th in D1
For 140: 110/140 or 130/140 or 140/150 or 140/170 could be the 9th in D1
For 210: 180/210 or 210/240 could be the 9th 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 9th 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
Click to expand...
I think you mean D. Yes?


Thanks
 
Upvote 0
Rick I’d be interested where you found only the 7
Click to expand...


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.:eeek:


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, ??/??, ??/??
 
Last edited:
Upvote 0
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, ??/??, ??/??
Click to expand...


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
 
Last edited:
Upvote 0
If not, what should be done?
Click to expand...

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
Click to expand...

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

Similar threads

T
  • Solved
Help on putting a number into a range
2 3
Replies
22
Views
325
Peter_SSs
Peter_SSs
J
  • Question Question
Sort X-Axis in PivotChart
Replies
2
Views
111
GraH
GraH
S
  • Question Question
Inventory disposal plan
Replies
3
Views
338
Spaceballs_the_fanboy
S
G
  • Solved
If Then else in Excel
Replies
9
Views
426
Gulfcourse
G
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

Forum statistics

Threads
1,226,797
Messages
6,193,055
Members
453,772
Latest member
aastupin

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