 # 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? 4 Replies

# Re: Sorting Bins while keeping objects in them

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

# Re: Sorting Bins while keeping objects in them

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

# Re: Sorting Bins while keeping objects in them

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 # Re: Sorting Bins while keeping objects in them

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