Split data with the help of VBA

Hello Everyone, I am trying to create a unique list from the given input. A sample data as input and corresponding output which is expected, I have attached here. any help on writing a vba would be ...

Book2.xlsx10 KB

Macros and VBA

HansVogelaar

Feb 15, 2022

Excel

Sub SplitData()
    Dim r As Long
    Dim s As Long
    Dim t As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("I5:M" & Rows.Count).Clear
    s = 4
    t = 4
    For r = 5 To m
        If Range("C" & r).Value <> "" And Range("D" & r).Value <> "" Then
            If Application.CountIfs(Range("C5:C" & m), Range("C" & r).Value, Range("D5:D" & m), "<>") = 1 Then
                s = s + 1
                Range("C" & r).Resize(1, 2).Copy Destination:=Range("I" & s)
            Else
                t = t + 1
                Range("C" & r).Resize(1, 2).Copy Destination:=Range("L" & t)
            End If
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

"Multiplicity=1" = LET( multiplicity, COUNTIFS(Cat_1,Cat_1, Cat_2,"<>"), FILTER(input, (multiplicity=1)*(Cat_2<>"")) ) "Multiplicity>1" = LET( multiplicity, COUNTIFS(Cat_1,Cat_1, Cat_2,"<>"), FILTER(input, (multiplicity>1)*(Cat_2<>"")) )

Forum Discussion

Split data with the help of VBA

Share

Resources