Macro to fill an identifier down and SUMIF

[xbrianx]

I have extracted some data from our employee database. It's 10,000+ rows. The first row of the employee is their name, a hypen, then their 9-digit SSN (11-digits including the two hyphens in the SSN). After each employee is 2-7 rows of deductions. The last row of the employee has the total amount. In column 'B there is the individual deduction name (irrelevant) and in column 'C' is the individual deduction amount - see attached image. I need to create a new tab that extracts the SSN in one column with the SUM of all their deductions in the next column. I tried using =RIGHT(A2, 11) to extract only the SSN, but I only want to copy/paste that formula for the employee names (cells A2, A5, A10, etc.). I then tried to use a SUMIF formula on column 'C' thinking that would be easier to get versus trying to pull the last row's total amount highlighted in yellow. Can you help?

 

[shina67]

To extract the SSN, if your data starts from row 2, you can use this formula in cell D2 and drag it down:

 =IF(MOD(ROW()-2,4)=0,RIGHT(A2,11),"")

This formula will only extract the SSN for every 4th row (since you mentioned the data has 2-7 rows of deductions between each employee).

To calculate the sum of deductions for each SSN, In cell E2, you can use the following formula and drag it down:

=IF(D2<>"",SUMIF($D$2:$D$10000,D2,$C$2:$C$10000),"")

This formula will sum the deductions in column C for each SSN in column D.

Make sure to adjust the range ($D$2:$D$10000 and $C$2:$C$10000) in the formulas to match your data. Also, adjust the row numbers accordingly if your data doesn't start from row 2.

 

[Fluff]

@shina67 you have already been warned about using AI to answer questions. If you continue you will get a ban.

 

[NoSparks]

Would have been better if you had used the MrExcel XL2BB add-in and posted what you actually have instead of a picture of it.
Any ways, with the test workbook I setup from your picture, this macro using a dictionary, seems to provide the results you're after.

Sub Testing()
   
    Dim lr As Long, i As Long
    Dim dic As Object, arr As Variant
    Dim SSN As String, ssn2 As String, item As Double
   
Set dic = CreateObject("Scripting.Dictionary")

With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    arr = .Range("A2:A" & lr)
End With

For i = 1 To UBound(arr)
    If i = 1 Then
        SSN = Right(arr(1, LBound(arr)), 11)
        item = 0
    End If
   
    If i > 1 And i < UBound(arr) Then
        If Len(arr(i, 1)) > 12 Then
            ssn2 = Right(arr(i, 1), 11)
            If ssn2 <> SSN Then
                item = arr(i - 1, 1)
            End If
        End If
    End If
   
    If i = UBound(arr) Then
        item = arr(i, 1)
    End If
   
    If item <> 0 Then
        dic.Add SSN, item
        SSN = ssn2
        item = 0
    End If
Next i

Worksheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet
    .Name = "Results"
    .Range("A1") = "Employee"
    .Range("A2").Resize(dic.Count) = Application.Transpose(dic.keys)
    .Range("B1") = "Total Amount"
    .Range("B2").Resize(dic.Count) = Application.Transpose(dic.items)
    .Columns.AutoFit
End With

End Sub

 

[xbrianx]

Would have been better if you had used the MrExcel XL2BB add-in and posted what you actually have instead of a picture of it.
Any ways, with the test workbook I setup from your picture, this macro using a dictionary, seems to provide the results you're after.

Sub Testing()
  
    Dim lr As Long, i As Long
    Dim dic As Object, arr As Variant
    Dim SSN As String, ssn2 As String, item As Double
  
Set dic = CreateObject("Scripting.Dictionary")

With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    arr = .Range("A2:A" & lr)
End With

For i = 1 To UBound(arr)
    If i = 1 Then
        SSN = Right(arr(1, LBound(arr)), 11)
        item = 0
    End If
  
    If i > 1 And i < UBound(arr) Then
        If Len(arr(i, 1)) > 12 Then
            ssn2 = Right(arr(i, 1), 11)
            If ssn2 <> SSN Then
                item = arr(i - 1, 1)
            End If
        End If
    End If
  
    If i = UBound(arr) Then
        item = arr(i, 1)
    End If
  
    If item <> 0 Then
        dic.Add SSN, item
        SSN = ssn2
        item = 0
    End If
Next i

Worksheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet
    .Name = "Results"
    .Range("A1") = "Employee"
    .Range("A2").Resize(dic.Count) = Application.Transpose(dic.keys)
    .Range("B1") = "Total Amount"
    .Range("B2").Resize(dic.Count) = Application.Transpose(dic.items)
    .Columns.AutoFit
End With

End Sub

@NoSparks - thanks for the reply. Sorry I was unable to use the MrExcel XL2BB add-in as it is blocked on our network. This macro works very well. How'd you do it?
-Brian