Jun 10 2022 09:19 AM
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?
Jun 10 2022 12:57 PM
Jun 11 2022 02:00 AM
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?
Jun 11 2022 12:50 PM - edited Jun 12 2022 04:11 AM
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
Jun 11 2022 03:48 PM
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)