Sorting rows: 6 transactions with date and action information grouped together

Question

I need to organize transaction information in a spreadsheet by ascending date order. Each row represents a single person and the last 6 transactions in their history. The problem is that while the database spits out the last 6 transactions, it doesn't do so in order, so I need to do it manually in Excel.

The transaction date and expiration date must stay tied to the "Is Gift" and "Action" data, so using row sorting functions doesn't work for this application. These four columns need to be kept together as a group.

In the attached screenshot, I've used colors to show how columns need to remain grouped together and highlighted in red, the transaction that SHOULD have come first if the data were in the correct order. Hopefully this gives a clearer indication of the issue ("firsts" are in different columns for every individual) and what my goal is (for each column group to be sorted, in ascending date order, by row).

Answer 1

Try this code. Paste this in VB Editor window and run it (google 'how to run vba code' for more help). This is not at all efficient, but it should work.

Sub Some_Sorting_Procedure()
    Dim r As Long, last_row As Long
    Dim i As Long, j As Long
    Dim arr As Variant

    Application.ScreenUpdating = False

    With ActiveSheet
        last_row = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = 2 To last_row
            For i = 3 To 23 Step 4
                For j = i + 4 To 23 Step 4
                    If .Cells(r, i).Value > .Cells(r, j).Value Then
                        arr = .Cells(r, j).Resize(, 4).Value
                        .Cells(r, i).Resize(, 4).Cut .Cells(r, j)
                        .Cells(r, i).Resize(, 4).Value = arr
                    End If
                Next j
            Next i
        Next r
    End With
End Sub

Edit

This assumes that your data starts at cell A1, and there are no empty cells in the first column. The hard-coded value 23 is because you mentioned there would be only six transactions. You'll need to increase this number by 4 for each extra transaction.