Forum Discussion

caezza's avatar
caezza
Copper Contributor
Jun 10, 2022

Sorting Bins while keeping objects in them

Hello,

 

I am trying to sort a list similar to the following. The bins need to be in order (1A,1B,1C,1D,1E, etc.) while the objects within the bin need to move with them. An example is supplied. The real list is tens of thousands of entries long, so a manual sort isn't really an option. Any suggestions?

 

excel

4 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 11, 2022

    caezza 

     

    I played with finding the SUM of the CODE and sorting but found it wasn't necessary.

    My LAMBDA take ('list' being a named range for the unsorted list):

    =DROP(SORT(HSTACK(SCAN("",MAP(list,LAMBDA(x,IF(ISERROR(SUM(LEFT(x))),"",x))),LAMBDA(a,b,IF(LEN(b)=0,a,b))),list),1,1),,1)

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 11, 2022

    caezza 

    As always, I set out to push Excel 365 to its limit!

     

    = LET(
    group, SCAN(0,list,
      LAMBDA(grp,entry,
         IF(LEFT(entry,1)="1",RIGHT(entry,1),grp))
      ),
    SORTBY(list,group)
  )

     

    Basically, the idea is to use a new array to copy the group captions down and then sorts by group.  With a cunning bit of conditional formatting

     

     

  • caezza's avatar
    caezza
    Copper Contributor
    Jun 10, 2022
    I just figured this out using a series of complicated nested =IF statements, feel free to suggest a better way if anyone knows it
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jun 11, 2022

      caezza 

      Sub sort()

Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim k As Integer
Dim posfind As Integer
Dim lngzeilemax As Integer
Dim search As String

On Error GoTo Endproc

Range("D:D").Clear

lngzeilemax = Range("B" & Rows.Count).End(xlUp).Row

k = 3
For j = 1 To 40
For i = 1 To 26

search = j & Chr(64 + i)
posfind = Application.WorksheetFunction.Match(search, Range(Cells(3, 2), Cells(lngzeilemax, 2)), 0) + 2

If i < 26 Then

For z = 1 To lngzeilemax


If IsNumeric(Left(Cells(posfind + z, 2), 1)) Then
Range(Cells(posfind, 2), Cells(posfind + z - 1, 2)).Copy Cells(k, 4)

k = Range("D" & Rows.Count).End(xlUp).Row + 1

Exit For

Else
End If

Next z

Else

For z = 1 To lngzeilemax


If IsNumeric(Left(Cells(posfind + z, 2), 1)) Then
Range(Cells(posfind, 2), Cells(posfind + z - 1, 2)).Copy Cells(k, 4)

k = Range("D" & Rows.Count).End(xlUp).Row + 1

Exit For

Else
End If

Next z
End If

Next i
Next j

Endproc:

End Sub

      Maybe with these lines of code. You can click the button in cell F2 in the attached file to start the macro.

       

      Can you share your nested IF statements?

Resources