Sorting VBA

[johnko]

Hello there! I need some help in sorting out data based on same one cell data value but still separate them apart because of different values from other cells, I will upload one copy of excel sheet and share the link here below for you guys to have a better understanding, inside the spreadsheet there is a "before" and "after" sheet name which indicates raw data and the results I want respectively. Thank you.

https://app.box.com/s/kc4pyx0nytcic0z3cx10rh85o45hey2r


eg. Before

A		G
CLIENT		CONTRACT
John		Apple
John		Apple
John		Apple
Aaron		Durian
David		Banana
John		Orange
John		Orange
John		Orange
David		Honey
David		Honey

<tbody>
</tbody>

Results:

A		G
John		Apple
John		Apple
John		Apple
John		Orange
John		Orange
John		Orange
Aaron		Durian
David		Banana
David		Honey
David		Honey

<tbody>
</tbody>

Noted that I want them to group together based on same cell value on "A" which is the Client's name but still want to leave a gap between them because of different cell value on "G" which is the contract name. Hope to hear from you guys soon thanks!

 

[JLGWhiz]

Give this a try

Sub t()
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    .UsedRange.Sort .Range("A2"), xlAscending, , .Range("G2"), xlAscending, , , xlYes
        For i = lr To 3 Step -1
            If .Cells(i, 1).Value <> .Cells(i - 1, 1).Value Then
                Rows(i).Insert
            End If
            If .Cells(i, 7).Value <> .Cells(i - 1, 7).Value And .Cells(i, 1) <> "" Then
                Rows(i).Insert
            End If
        Next
End With
End Sub

 

[johnko]

Give this a try

Sub t()
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    .UsedRange.Sort .Range("A2"), xlAscending, , .Range("G2"), xlAscending, , , xlYes
        For i = lr To 3 Step -1
            If .Cells(i, 1).Value <> .Cells(i - 1, 1).Value Then
                Rows(i).Insert
            End If
            If .Cells(i, 7).Value <> .Cells(i - 1, 7).Value And .Cells(i, 1) <> "" Then
                Rows(i).Insert
            End If
        Next
End With
End Sub

This works like a charm! Thank you!

 

[JLGWhiz]

This works like a charm! Thank you!

Yoou're welcome,
regards, JLG