Sorting Bins while keeping objects in them

Copper Contributor

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?

 

Clear Example.png

4 Replies
I just figured this out using a series of complicated nested =IF statements, feel free to suggest a better way if anyone knows it

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

@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

 

image.png

 

@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)