Dec 08 2020 09:14 AM
Raw data scanned into inventory in column A where Inventory numbers in Cells 1,3,6 have to be sorted numerically from smallest to largest while keeping attached serial numbers in Cells 2,4,6 to the sorted Cells 1,3,6 as shown in Column B.
What's the fastest way to do this if I have hundreds of cells in column A with inventory numbers and associated serial numbers?
A B (Intended result)
Cell1 6 3
Cell2 serial123 serial189
Cell3 7 6
Cell4 serial126 serial123
Cell5 3 7
Cell6 serial189 serial126
Dec 08 2020 11:50 AM
Run this macro. Please test on a copy of your worksheet first.
Sub SortData()
Dim m As Long
Dim v() As Variant
Dim i As Long
Dim j As Long
Dim tmp1 As Long
Dim tmp2 As String
m = Range("A" & Rows.Count).End(xlUp).Row
v = Range("A1:A" & m).Value
For i = 1 To m - 2 Step 2
For j = i + 2 To m Step 2
If v(i, 1) > v(j, 1) Then
tmp1 = v(i, 1)
tmp2 = v(i + 1, 1)
v(i, 1) = v(j, 1)
v(i + 1, 1) = v(j + 1, 1)
v(j, 1) = tmp1
v(j + 1, 1) = tmp2
End If
Next j
Next i
Application.ScreenUpdating = False
Range("A1:A" & m).Value = v
Application.ScreenUpdating = True
End Sub
Dec 08 2020 01:37 PM
@Hans Vogelaar well that looks complex but I'll try it out. Thanks for the solution.
Dec 08 2020 01:40 PM
You don't really have to understand the code.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the code from my previous reply into the module.
With the insertion point anywhere in the code, press F5 to run it.
Switch back to Excel to view the result.
Dec 08 2020 01:44 PM
@Hans Vogelaar OK tomorrow I will try it out when I have to try and sort 400+ inventory tag numbers while keeping their associated serial numbers in the cell below each tag number. Thanks again!
Dec 08 2020 02:02 PM