Sort groups of 4 numbers

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
 
...
so that if one of the groups was 10/80/30/15, you would want it to become 10/15/30/80 ...
Like, if 10/80/30/15 were in A1, you can get the sorted version in A2 with:
Code:
Sub sort()
Dim b() As Boolean, c, u
ReDim b(80)
For Each c In Split([a1], "/")
    If c > UBound(b) Then ReDim Preserve b(c)
    b(c) = True
Next c
For c = 1 To UBound(b)
    If b(c) Then u = u & "/" & c
Next c
[a2] = Mid(u, 2)
End Sub
This is if the / separated numbers are unique as in the example. But v easily modified if they're not.
 
Last edited:
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
what I now think you are asking for is to sort the individual numbers

No Rick sorry for any confusion. Still sort the quads only by the leading (1st) number.

Remember the sets (say 4 sets) will be given in a random order.
 
Last edited:
Upvote 0
That wasn't very clear.

Try this.


This would be the sorted result I seek: 5/10/20/30, 40/50/70/80, 130/140/160/170, 200/210/230/240 which is chosen from ALL the sets (23) in the (new) UDF.
 
Last edited:
Upvote 0
More..........

1: The ‘Quad sets’ are randomly chosen.

2: They must not repeat.

3: The result should be ascendingly sorted only by the leading number of each ‘Quad’

4: The quads themselves don’t have to be sorted

I realize the 23 quad sets I provided are themselves sorted and that is irrelevant as other larger sets which I will use at a later time will not be themselves, ascendingly sorted.

The only number to consider for the sorting is the leading number in ‘each quad set’
 
Upvote 0
More..........

1: The ‘Quad sets’ are randomly chosen.

2: They must not repeat.

3: The result should be ascendingly sorted only by the leading number of each ‘Quad’

4: The quads themselves don’t have to be sorted

I realize the 23 quad sets I provided are themselves sorted and that is irrelevant as other larger sets which I will use at a later time will not be themselves, ascendingly sorted.

The only number to consider for the sorting is the leading number in ‘each quad set’
Okay, I see... only one question left. Earlier you said...

This would be wrong:
=Quads(3) Result 10/20/40/50, 40/50/70/80, 200/210/230/240 (40 & 50 repeat)

You told us what was wrong, but not what it should be. What result should the code give for that set of quads?
 
Upvote 0
What result should the code give for that set of quads?


Rick that result should never be given as it is not unique (no number is ever repeated in a result - each set of 4 must be completely unique from any other set ). In that wrong example 40 & 50 repeat so, as said, it would never be given.

I should have said it would never be actually given.


Thanks
 
Last edited:
Upvote 0
Rick that result should never be given as it is not unique (no number is ever repeated in a result - each set of 4 must be completely unique from any other set ). In that wrong example 40 & 50 repeat so, as said, it would never be given.

I should have said it would never be actually given.
Sorry to keep pressing, but remember, I know nothing about what you are doing so I need you to fill in all the parts that I think will be critical in the code I write. So, to follow up, if you had a cell with 23 quads in and if any number in one quad matched that same number in any other quad (no matter what position), the code should skip over that cell and report nothing for it (even though 21 of the quads were unique)... is that correct?
 
Upvote 0
Sorry to keep pressing
That's fine

Yes, that is correct.


I don't wish to confuse you but....
Did you check out the Pairs UDF code given above as that is exactly what is does but doesn't give leading number sorted results?
 
Last edited:
Upvote 0
I don't wish to confuse you but....
Too late... :confused:


Did you check out the Pairs UDF code given above as that is exactly what is does but doesn't give leading number sorted results?
I just looked at the Pairs UDF and it returns something unexpected (at least to me). I thought you had cells with quads (4 numbers delimited by slashes) and you wanted them sorted by the first number but only if all the numbers were unique. The Pairs UDF takes a number from the user as its argument and generates that many random pairs (with a single slash between them) where the pairs are taken from a pre-made list of pairs. What happened to the quads???
 
Upvote 0
1st: The UDF you gave in #18 is quite correct and is what I need for cells which contain quads that are ALL unique and the result just needs to be sorted. It’s fine and will be used separate from below.


2nd: This new UDF needs to use a list of quad sets within the UDF itself which could very well have non-unique sets which the list of 23 above has.

It is essentially the same principal as the Pairs UDF only it (pairs udf) uses sets of pairs (58) and many are not unique and doesn’t sort the result.

For this new UDF if you could incorporate this list (the 23 quads) into the UDF code and have it choose random, unique sets and then sort the result by the leading number. The number of sets chosen will be dictated by the function as in say =Quads(5) or =Quads(3) or whatever.

If I ever need to change the list in the UDF I assume I only have to change that section of the code.


I’m really sorry for the confusion
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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