CODEWITHSUNDEEP
excelvba
Ryan
Ryan

Reputation: 13

In Excel, how can I separate duplicate values to display in a cleaner format?

I might be asking this incorrectly, but I'm definitely no excel expert. Is there a faster way to separate duplicate values in excel post sorting? See attached images of what I need. Image 1 shows raw data while Image 2 shows how I'd like the data to be formatted, but without doing it manually.

Pre

Post

Upvotes: 0

Views: 43

Answers (1)

VirtualMichael
VirtualMichael

Reputation: 721

With a macro enabled workbook (.xlsm), you can cycle through each row, detect changes in a given column, then insert a blank line above when a change is detected. See the following code:

Sub SeparateGroups()
    Dim lastRowIdx As Integer
    lastRowIdx = Range("A1").End(xlDown).Row()
    Dim testRowIdx As Integer
    Dim previousData As String
    Dim testData As String
    previousData = Range("A1").Value
    For testRowIdx = 2 To lastRowIdx
        testData = Range("A" & testRowIdx).Value
        If (testData <> previousData) Then
            Rows(testRowIdx).Insert shift:=xlShiftDown
            lastRowIdx = lastRow + 1
        End If
        previousData = testData
    Next
End Sub

Upvotes: 1

Related Questions