VBA Duplicate Values in one Column/Sum occurrences in second column off criteria

[goin2daship]

I have a bunch of IP Addresses in Column C that are in consecutive order containing duplicates. In Column D I have a text value that could be 1 of 4 items (Critical, High, Medium, Low). I need to delete the Duplicate IPs and Sum the occurrences of the criteria in another Worksheet to the same Row. I used to different button to do this. The first to analyze Column C and transfer copy IPs to WS4 minus duplicate. The second command I created a loop to attempt to sum up each Criteria (Critical and High are same) and place in same row as number of IP occurrences. Code is below (sheet's 3 is original data, sheet 4 should be output but the Loop is skipping lines and I don't know why).

Private Sub CommandButton1_Click()


a = Worksheets("Sheet3").Cells(Rows.Count, 3).End(xlUp).Row
cnt = 2


For i = 2 To a


If Worksheets("Sheet3").Cells(i, 3).Value <> Worksheets("Sheet3").Cells(i - 1, 3).Value Then
Worksheets("Sheet3").Cells(i, 3).Copy
Worksheets("Sheet4").Activate
b = Worksheets("Sheet4").Cells(Rows.Count, 3).End(xlUp).Row
Worksheets("Sheet4").Cells(b + 1, 3).Select
ActiveSheet.Paste
Worksheets("Sheet3").Activate

ElseIf Worksheets("Sheet3").Cells(i, 3).Value = Worksheets("Sheet3").Cells(i - 1, 3).Value Then

End If

Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet3").Cells(1, 1).Select

End Sub


Private Sub CommandButton2_Click()


C = Worksheets("Sheet3").Cells(Rows.Count, 3).End(xlUp).Row
Dim Low, Medium, High As Integer
Dim NextIP As Long
NextIP = 2
cnt = 2


For s = cnt To C


Do While Worksheets("Sheet3").Cells(s, 3).Value = Worksheets("Sheet3").Cells(cnt - 1, 3).Value

If Cells(cnt, 4).Value = "low" Then
Low = Low + 1
ElseIf Cells(cnt, 4).Value = "medium" Then
Medium = Medium + 1
ElseIf Cells(cnt, 4).Value = "high" Then
High = High + 1
ElseIf Cells(cnt, 4).Value = "critical" Then
High = High + 1
End If

cnt = cnt + 1

Loop

Worksheets("Sheet4").Cells(NextIP, 4).Value = Worksheets("Sheet4").Cells(NextIP, 4).Value + Low
Worksheets("Sheet4").Cells(NextIP, 5).Value = Worksheets("Sheet4").Cells(NextIP, 5).Value + Medium
Worksheets("Sheet4").Cells(NextIP, 6).Value = Worksheets("Sheet4").Cells(NextIP, 6).Value + High

NextIP = NextIP + 1
Low = 0
Medium = 0
High = 0

Next

End Sub

 

[Fluff]

Hi & welcome to MrExcel.

When an IP is duplicated will it always have the same value in col D? Or could you have an IP with High & the same IP with Low?

 

[goin2daship]

Hi,

Column D is always going to have 5 different types of Text Values (Critical, High, Medium, Low, Info). I usually omit the info one by just a basic filter to make it easy. What i'm trying to do is create a new worksheet that has the occurrences of Column D associated with each IP. So if I have an IP show 5 times in column C with 2 Lows, 2 Mediums and 1 High I would want on the next worksheet to display a Row With Column C being the IP, Column D being the Sum of # of occurrences of High, Column E being the Sum of occurrences of Medium, and Column F being the Sum of Occurrences of Low. I have no experience in Excel and am trying to learn on the fly.

 

[Fluff]

Ok, how about

Sub RemoveDupesandcount()

   Dim Cl As Range
   Dim Lvl As Long
   Dim Ky As Variant, tmp
   Dim Ws3 As Worksheet
   Dim Ws4 As Worksheet
   
   Set Ws3 = Sheets("Sheet3")
   Set Ws4 = Sheets("Sheet4")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws3.Range("C2", Ws3.Range("C" & Rows.Count).End(xlUp))
         Select Case LCase(Cl.Offset(, 1).Value)
            Case "low": Lvl = 0
            Case "medium": Lvl = 1
            Case Else: Lvl = 2
         End Select
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(0, 0, 0)
            tmp = .Item(Cl.Value)
            tmp(Lvl) = tmp(Lvl) + 1
            .Item(Cl.Value) = tmp
         Else
            tmp = .Item(Cl.Value)
            tmp(Lvl) = tmp(Lvl) + 1
            .Item(Cl.Value) = tmp
         End If
      Next Cl
      For Each Ky In .keys
         Ws4.Range("C" & Rows.Count).End(xlUp).Offset(1, 1).Resize(, 3).Value = .Item(Ky)
         Ws4.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Ky
      Next Ky
   End With
      
End Sub

 

[goin2daship]

This worked perfectly. So i'm going to do some research on the Case command. Thanks for dedicating your time to assist.

 

[Fluff]

Glad to help & thanks for the feedack

For more info on Select Case statements, have a look here https://www.exceltrick.com/formulas_macros/vba-select-case-statement/