Forum Discussion
Solved
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 ...
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
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 SubThe code starts by finding the last used row in columns C and D. This is assigned to the variable m.
Next, it removes existing results in columns I to M below row 4.
The variables s and t are initialized to 4 (the header row for the results). s is for columns I and J, t for columns L and M.
The variable r loops through rows 5 to m. If both column C and column D in row r are not blank, we use COUNTIFs to check whether the value in column C occurs only once, with a non-blank value in D, or more than once. If once, increase the row number s and copy C / D to I /J, otherwise increase the row number t, and copy C / D to L / M.
Thank you so much sir.
- Thank you so much sir.
