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

 

[kalak]

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

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.

 

[MaxTrax]

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.

 

[MaxTrax]

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.

 

[MaxTrax]

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’

 

[Rick Rothstein]

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?

 

[MaxTrax]

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

 

[Rick Rothstein]

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?

 

[MaxTrax]

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?

 

[Rick Rothstein]

I don't wish to confuse you but....

Too late...

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???

 

[MaxTrax]

1^st: 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.


2^nd: 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