none
Order and grouped data RRS feed

  • Question

  • Is it possible to sort and group data from the table on the left, to look like the table on the right?

    I prefer with formulas, but if it is not possible it can be with VBA.

    Attach sample file: Download

    Thanks

    Thursday, April 2, 2020 3:30 PM

Answers

  • Here is a macro:

    Sub CleanUp()
        Dim rng As Range
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        Set rng = Range("A3").CurrentRegion
        rng.Sort Key1:=Range("A3"), Key2:=Range("C3"), Header:=xlYes
        m = Range("A" & Rows.Count).End(xlUp).Row
        For r = m To 3 Step -1
            If Range("A" & r).Value = Range("A" & r - 1).Value Then
                Range("A" & r).Resize(1, 3).ClearContents
            Else
                Intersect(Rows(r), rng).Borders(xlEdgeTop).LineStyle = xlContinuous
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by skindu Friday, April 3, 2020 3:45 PM
    Thursday, April 2, 2020 8:35 PM