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

 

[MaxTrax]

not refering to the previous thread until post #8.

I started this thread because it is completely different than the other thread.
On that thread the exact numbers/pairs were known. Here they are not and are of a different structure (Sets of 4). If the exact quads were known, I could have modified the UDF from that thread to suit and then I still would have had to asked how to sort the result. As I did on that thread yesterday but regarding that data and that code.

Here, all I asked for was a way to sort an example of six sets of 4 numbers and not to actually give the sets. If I would have asked the same thing on that thread I would have been told to start a new thread.
The reason I didn’t refer you to the other thread until post #8 is because we were only discussing sorting by way of a formula. Then you mentioned in post #6.

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

So then I naturally referred you to that thread as a possible means of helping or assisting you to develop a VBA script for the purpose discussed here.

It is really unfortunate you feel mislead by me. That certainly was not my intention at all. I was, as mentioned, only trying to help.

If I may return to the formula you have given I am still having trouble with it.
1: A1 holds the 6 sets of quads as per my example. Also tried 9 sets
2: I have defined all names (and rechecked them) that you set out.
3: I have entered your code in A2 (and tried B2) and checked that it refers to A1 and certain names.
4: Result is #Ref!

Here is the code from my test sheet:

=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)),"")

Further info, if that helps, the formula will be used approximately every 18 row and every 7^th column in that row. The unsorted data would be in the 17th row, 7th column (Cell above).

How would I best use your formula given the above dimensions?


Thanks for any help.

 

[MarcelBeug]

OK, fair enough.

With regard to a VBA solution, I slightly adjusted the code provided by kalak in post #10 and made it a function, so you can call it in any cell, e.g.: =sortGroups(A1)

Function sortGroups(ByVal unSorted As String) As String

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

For j = 1 To n
    For Each e In Split(unSorted, ", ")
        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

sortGroups = Mid(x, 3)

End Function

I ran a speed test and noticed this code is even slower than the formula solution.
I guess it would be different if all input cells could be read into an array variable in VBA, do all the processing and than output the results to the worksheet.
As I understand the information is rather scattered, this is not possible.

So back to the formula solution: it is important to first select the cell where the formula will reside, so A2 where the input is in A1, and than define all the names.
So when you are in A2, all names should be exactly equal to th definitions is post #7.
So if you have the names defined and cell A2, you should verify all the defined names (except for Sequence), refer to cell A1.

If you still get #REF! errors, you can use "Evaluate formula" on the formulas tab to verify the faulty part.

If all OK, you can copy the formula to any cell where it is required. This must be a cell just underneath the cell with the input string.
Or you define a name for the formula as well, as indicated in post #7.

Hope this helps.

 

[MaxTrax]

Great Guys. That is exactly what I’m seeking for the UDF. Thank you both

Marcel I will re-check the formula and defined names in my sheet again shortly.

Obviously I’m doing something wrong as it works for you.

Also, and separate from above, could your code be incorporated into this UDF to give a sorted result instead of an unsorted result? If possible that would be really appreciated and of great help.

[COLOR=darkblue]Function[/COLOR] Pairs(howmany [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Static[/COLOR] origprs [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
  
  [COLOR=darkblue]Dim[/COLOR] remprs [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], num [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], thesenums [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] s [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], tmp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  
  Application.Volatile
  Randomize
  [COLOR=darkblue]If[/COLOR] IsEmpty(origprs) [COLOR=darkblue]Then[/COLOR] 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/ /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/")
  remprs = origprs
  [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] howmany
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](remprs) = -1 [COLOR=darkblue]Then[/COLOR]
      Pairs = "Error"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Function[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    s = remprs(Int(Rnd() * (UBound(remprs) + 1)))
    s = Mid(s, 2, Len(s) - 2)
    tmp = tmp & ", " & s
    thesenums = Split(s, "/")
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] num [COLOR=darkblue]In[/COLOR] thesenums
      remprs = Filter(remprs, "/" & num & "/", [COLOR=darkblue]False[/COLOR])
    [COLOR=darkblue]Next[/COLOR] num
  [COLOR=darkblue]Next[/COLOR] i
  Pairs = Mid(tmp, 3)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

From here: http://www.mrexcel.com/forum/excel-questions/936216-give-random-unique-set-9-pairs.html
Post #10


Thanks

 

[MarcelBeug]

For the next 10 hours or so I'll be offline.

With regard to adjustment of the code in post #13 my suggestion would be to have the pairs generated in ascending order somehow, instead of first creating the string with all pairs and after that, sort the string.

I understand this is different because these are pais and not quads?
The original quad question is in this thread: http://www.mrexcel.com/forum/excel-...unique-set-numbers-[4-quads-plus-1-pair].html
Wouldn't you want something similar for quads?
And how about a complete formula solution?

If no reactions in the meantime, I'll take a look this evening CEST.

 

[kalak]

...
I ran a speed test and noticed this code is even slower than the formula solution.
...

mmm...
I just posted that code for interest because a formula was actually asked for and I didn't expect a speed test.

If you want a version than runs faster than the formula, try:

Sub last_resort()

Const n& = 360
Dim b(n) As Boolean, e, x(1 To n, 1 To 2)
Dim i As Long, j As Long, k as Long,  m As Long
For Each e In Split([a1], ",")
    If InStr(e, "/") > 0 Then
        k = k + 1
        x(k, 1) = Left(e, InStr(e, "/") - 1)
        x(k, 2) = e
        If x(k, 1) > m Then m = x(k, 1)
        b(x(k, 1)) = True
    End If
Next e

For j = 1 To m
    If b(j) Then
        For i = 1 To k
            If j = x(i, 1) Then y = y & ", " & x(i, 2)
        Next i
    End If
Next j
[a2] = Mid(y, 3) & ","

End Sub

 

[MaxTrax]

my suggestion would be to have the pairs generated in ascending order

Yes that would be ideal if it can be done. It's beyond me.

Wouldn't you want something similar for quads?

Yes, definitely, BUT as stated earlier in the thread I don't have the complete quad sets unlike the complete pairs which I do have.

And how about a complete formula solution?

Well yes if that's worth pursuing.

If no reactions in the meantime, I'll take a look this evening

Ok and thanks.

 

[MaxTrax]

Hi kalak,

Thank you for your contribution so far. You really nailed it. Can you modify your latest code to a UDF so it can be used anywhere as Marcel did with your 1st code?


Would you also consider trying to achieve what Marcel suggested for the UDF code I supplied in #13 above?
That is for the code to give the result in ascending order by the leading number in each pair for the set of 9 pairs. As it is now the result is in unsorted order.

Thanks (I'm offline for 8 hrs)

 

[Rick Rothstein]

Here is a UDF (user defined function) that will sort the values from the cell passed into it (I can rewrite it as a macro if you want and if you tell me what cells to apply it to)...

[table="width: 500"]
[tr]
	[td]Function SortQuads(S As String) As String
  Dim X As Long, Y As Long, Temp1 As String, Temp2 As String, Quads() As String
  Quads = Split(Replace(S, " ", ""), ",")
  For X = 0 To UBound(Quads)
    For Y = X To UBound(Quads)
      If Val(Quads(Y)) < Val(Quads(X)) Then
        Temp1 = Quads(X)
        Temp2 = Quads(Y)
        Quads(X) = Temp2
        Quads(Y) = Temp1
      End If
    Next
  Next
  SortQuads = Join(Quads, ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SortQuads just like it was a built-in Excel function. For example,

=SortQuads(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[MaxTrax]

Thank you Rick for the UDF. It is perfect.

However overnight (for me) I have been able to obtain one small total ‘sets of quads’ in question.

They are these (23 sets).
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

Can these now be used in a UDF to give a random result and sorted by the leading number of each quad set?
Also note, and this is important, the result must only contain unique quads (no number can repeat)

The function should give results such as these examples:
=Quads(4) Result 50/60/80/90, 160/170/190/200, 220/230/250/260, 310/320/340/350

=Quads(2) Result 170/180/200/210, 290/300/320/330

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

What I’m asking for here is essentially similar code as that supplied in my post #13 only these are quads and the result is sorted. Have a look there.

Please note, and at the risk of repeating myself, I have also asked in my post #17 if the UDF given in #13 (Pairs) could be modified to give sorted results instead of unsorted?


There will be much larger quad sets used later but I can either incorporate them into the UDF I’m asking you for here or I can just use your UDF you supplied in #18 for them. We won’t worry about them now.


Thanks

 

[Rick Rothstein]

Please note, and at the risk of repeating myself, I have also asked in my post #17 if the UDF given in #13 (Pairs) could be modified to give sorted results instead of unsorted?

I think I misunderstood that to mean you wanted the groups sorted among themselves... what I now think you are asking for is to sort the individual numbers within each group (so that if one of the groups was 10/80/30/15, you would want it to become 10/15/30/80), correct?