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

 

[MarcelBeug]

That will be a rather complex formula so let's have the details clear:
1. Will there always be 6 groups of 4 numbers?
2. Will the last character of A1 always be a comma?
3. In the result: you don't want the trailing comma?
4. Will the leading numbers always be different or are duplicates possible?

 

[MarcelBeug]

Anyhow, if:
1. Yes
2. Yes
3. No trailing comma
4. Always different

Then define names:

ivec	=ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,1+LEN(Sheet1!$A$1)))
Leaders	=--MID(Sheet1!$A$1,Starts,Slashes-Starts-1)
Lengths	=1+FIND(","," "&Sheet1!$A$1,Starts)-Starts
Sequence	=MATCH(SMALL(Leaders,ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,6))),Leaders,0)
Slashes	=FIND("/"," "&Sheet1!$A$1,Starts)
Starts	=SMALL(IF(MID(" "&Sheet1!$A$1,ivec,1)=" ",ivec),ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,6)))

Formula in A2:

=MID(A1&" ",INDEX(Starts,INDEX(Sequence,1)),INDEX(Lengths,INDEX(Sequence,1)))&
MID(A1&" ",INDEX(Starts,INDEX(Sequence,2)),INDEX(Lengths,INDEX(Sequence,2)))&
MID(A1&" ",INDEX(Starts,INDEX(Sequence,3)),INDEX(Lengths,INDEX(Sequence,3)))&
MID(A1&" ",INDEX(Starts,INDEX(Sequence,4)),INDEX(Lengths,INDEX(Sequence,4)))&
MID(A1&" ",INDEX(Starts,INDEX(Sequence,5)),INDEX(Lengths,INDEX(Sequence,5)))&
MID(A1&" ",INDEX(Starts,INDEX(Sequence,6)),INDEX(Lengths,INDEX(Sequence,6))-2)

 

[MaxTrax]

Sorry for the delay in replying.

1. Will there always be 6 groups of 4 numbers?

No. Could be up to 9 groups.

2. Will the last character of A1 always be a comma?

No. That was a typo error.

3. In the result: you don't want the trailing comma?

No trailing comma.

4. Will the leading numbers always be different or are duplicates possible?

Always different and FYI all other numbers are different.



Not quite with you on the 'define names'. Is this VB code?


I also should have added this formula will be used thousands of times in one worksheet so maybe it is too complex. Not sure.

 

[MaxTrax]

Ok. I entered the defined names as set out but I'm getting #REF! error for formula result.

 

[MarcelBeug]

I also should have added this formula will be used thousands of times in one worksheet so maybe it is too complex. Not sure.

Probably it will be too complex and you'll need VBA code. I can take a look later.

How ill your data be arranged?
A1 Input
A2 Output
B1 Input
B2 Output
and so on, or
A1 Input
A2 Output
A3 Input
A4 Output
or otherwise (how?)?

 

[MarcelBeug]

Taking into account your answers, I adjusted the defined names and added 1 name for the number of Groups.
These names are now defined with relative references to cell A1, meaning:
if the input is in A1, first select the cell where you want the results from A1, so probably A2 or B1.

Then enter the defined names (Tab FORMULES - Name Manager):

Groups	=1+LEN(Sheet1!A1)-LEN(SUBSTITUTE(Sheet1!A1," ",""))
ivec	=ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,1+LEN(Sheet1!A1)))
Leaders	=--MID(Sheet1!A1,Starts,Slashes-Starts-1)
Lengths	=1+FIND(","," "&Sheet1!A1&",",Starts)-Starts
Sequence	=MATCH(SMALL(Leaders,ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,Groups))),Leaders,0)
Slashes	=FIND("/"," "&Sheet1!A1,Starts)
Starts	=SMALL(IF(MID(" "&Sheet1!A1,ivec,1)=" ",ivec),ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,Groups)))

Formula in output cell:

=IF(Groups>=1,MID(A1&", ",INDEX(Starts,INDEX(Sequence,1)),INDEX(Lengths,INDEX(Sequence,1))-2*(Groups=1)),"")&
IF(Groups>=2,MID(A1&", ",INDEX(Starts,INDEX(Sequence,2)),INDEX(Lengths,INDEX(Sequence,2))-2*(Groups=2)),"")&
IF(Groups>=3,MID(A1&", ",INDEX(Starts,INDEX(Sequence,3)),INDEX(Lengths,INDEX(Sequence,3))-2*(Groups=3)),"")&
IF(Groups>=4,MID(A1&", ",INDEX(Starts,INDEX(Sequence,4)),INDEX(Lengths,INDEX(Sequence,4))-2*(Groups=4)),"")&
IF(Groups>=5,MID(A1&", ",INDEX(Starts,INDEX(Sequence,5)),INDEX(Lengths,INDEX(Sequence,5))-2*(Groups=5)),"")&
IF(Groups>=6,MID(A1&", ",INDEX(Starts,INDEX(Sequence,6)),INDEX(Lengths,INDEX(Sequence,6))-2*(Groups=6)),"")&
IF(Groups>=7,MID(A1&", ",INDEX(Starts,INDEX(Sequence,7)),INDEX(Lengths,INDEX(Sequence,7))-2*(Groups=7)),"")&
IF(Groups>=8,MID(A1&", ",INDEX(Starts,INDEX(Sequence,8)),INDEX(Lengths,INDEX(Sequence,8))-2*(Groups=8)),"")&
IF(Groups>=9,MID(A1&", ",INDEX(Starts,INDEX(Sequence,9)),INDEX(Lengths,INDEX(Sequence,9))-2*(Groups=9)),"")

If so desired, you can put this formula in a defined name as well, e.g. SortedGroups, so in the output cells you'll have: =SortedGroups

 

[MaxTrax]

How ill your data be arranged?

I think I need to inform you about the workbook setup regarding these quad sets. They are very similar to the below link.

These unsorted ‘quad sets’ are given by vba code which I am not privileged to.

The example I gave was just that. An example.

I do know they are chosen in sets of four from numbers between 5 and 360 with minimum of 5 steps between each. Unfortunately, I don’t know how the sets are chosen. Whether randomly or definite sets. I would imagine definite sets and they always are in ascending order. Example 5/15/20/30. Not 5/30/15/20.

Not sure if I can obtain the sets as I know that would help you a great deal.

Here I must add I do have a UDF (but only for pairs) given by Peter SSs. Yesterday I also asked if that code “Pairs(9)” could be modified to give ‘sorted results’ AND also asked for another (or adjusted ‘Pairs(9)’ udf) to give a different set of unique pairs of 9, other than that given by “Pairs(9) AND only after other certain results were given (Match), There has been no answer yet and that is another question. Though it may help you with a new UDF etc.

Perhaps you could take a look at that for ideas. Please ask if you require more info.


That thread is here:
http://www.mrexcel.com/forum/excel-questions/936216-give-random-unique-set-9-pairs.html
Post #29. UDF code at #10. Pairs are here #1

 

[MarcelBeug]

You can select B1 and than define names and formula from post #7.
I ran a test and copied data thro row 10,000 which took almost 30 seconds on my laptop.

So far my contribution to this topic as I feel mislead by you not refering to the previous thread until post #8.

 

[kalak]

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

Not a formula, but here a bit of VBA code you could try if so inclined:

Sub resort()
Const n = 360
Dim b(n) As Boolean, j As Long, e, g, x

For j = 1 To n
    For Each e In Split([a1], ",")
        If Len(e) > 0 Then g = Split(e, "/")(0)
        If (Not b(g)) * (g = j) Then x = x & ", " & e: b(g) = True
    Next e
Next j

[a2] = Mid(x, 3) & ","

End Sub