Complex VBA Sorting

[FrenchCelt]

Hello,

I'm trying to find a way to code VBA to sort a report by two categories, simple enough, but the added wrinkle is that there is an extra row that has to be included with the sort, and it throws everything off.

This is a snippet of the original data:

I'm trying to sort username alphabetically by shift, so the end result should look like this:

The shift order should be 10A, 10B, 12A, and 12B.

If I simply sort by those two categories, of course, this is what happens:

The data is put in an order that renders the report useless, so clearly I need to keep the user data together as a unit while sorting, but I'm at a loss to figure out how.

Does anyone have any suggestions?

 

[Akuini]

Could you post your first table using XL2BB ?
So we can easily copy the data to test it.

 

[Akuini]

I'm using my own example:
The code:

Sub a1157370a()

'https://www.mrexcel.com/board/threads/complex-vba-sorting.1157370/

Dim i As Long, n As Long
Dim h As String
Dim va

n = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range("B2:B" & n)

    For i = 2 To UBound(va, 1)
        If va(i, 1) = "" Then va(i, 1) = va(i - 1, 1)
    Next
    
h = "E"  'helper column, change to suit

Range(h & "2").Resize(UBound(va, 1), 1) = va
Range("A2:" & h & n).Sort Key1:=Range(h & "2"), Order1:=xlAscending, Header:=xlNo
Range(h & "2").Resize(UBound(va, 1), 1).ClearContents

End Sub

Book1
	A	B	C
1	col1	col2	col3
2		B	Mark
3			Ford
4			Preston
5	vv
6		A	Joe
7			Ford
8	kk
9		B	Ford
10			Preston
11	zz
12		A	Hunter
13			Joe
14	nn
Sheet1

the result

Book1
	A	B	C
1	col1	col2	col3
2		A	Joe
3			Ford
4	kk
5		A	Hunter
6			Joe
7	nn
8		B	Mark
9			Ford
10			Preston
11	vv
12		B	Ford
13			Preston
14	zz
Sheet2

Note: col E is the helper column, change to suit:

h = "E" 'helper column, change to suit

 

[FrenchCelt]

Could you post your first table using XL2BB ?
So we can easily copy the data to test it.

I sent an email to IT asking if this is something I can add. It might be a day or two before I get a response.

 

[Akuini]

OK.
Did you tried the code in post #3 on your data?
You can change helper column from E to N in this line:
h = "E" 'helper column, change to suit

 

[FrenchCelt]

I will shortly. What does the helper column do?

 

[Akuini]

The code writes some data to helper column then use it to sort the main data.
But it's only a temporary column, it will be deleted at the end of the code, so you won't see it unless you run the code from Debug menu (in VBE) and stop the code (using breakpoint) in this line:
Range(h & "2").Resize(UBound(va, 1), 1).ClearContents

 

[FrenchCelt]

Thanks. I really like understanding what the code does so I can adapt the concept for future macros. That's pretty much how I've been learning. I'll let you know how it goes once I get some breathing room here at work.

 

[FrenchCelt]

The code works as intended. Thank you very much!

This is what it looks like after running the macro:

The only thing I'll have to figure out is how to clean up the data in Columns A & B so it doesn't look so uneven (compare to how user dmcmillion looks as opposed to bcox and jabowman). The other columns look fine (I have code later on that adds a row between usernames, that much is deliberate).

 

[Akuini]

@FrenchCelt
Sorry, the code is actually flawed, because it doesn't sort the name just shift.
So try his one instead:
Note: you need 2 helper columns now, so I use h = "O"

Sub a1157370b()

'https://www.mrexcel.com/board/threads/complex-vba-sorting.1157370/

Dim i As Long, n As Long
Dim h As String
Dim va

n = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range("A2:B" & n)

    For i = 2 To UBound(va, 1)
        If va(i, 1) = "" Then va(i, 1) = va(i - 1, 1)
        If va(i, 2) = "" Then va(i, 2) = va(i - 1, 2)
    Next
    
h = "O"  'helper column, change to suit

    With Range(h & "2").Offset(, -1).Resize(UBound(va, 1), 2)
        .Value = va
        Range("A2:" & h & n).Sort Key1:=Range(h & "2").Offset(, -1), Order1:=xlAscending, Header:=xlNo
        Range("A2:" & h & n).Sort Key1:=Range(h & "2"), Order1:=xlAscending, Header:=xlNo
        .ClearContents
    End With
End Sub