Number down column based on criteria of another and add up value

[mikesmoua]

This is what I have.

s 2
s 3
s 1
s 7
s 0
s 8
s 8
f 3
f 1
f 5
f 6
f 4
h 0
h 8
h 9
u 6
u 7
u 8
u 10


This is what I will need.


1 s 2 29
1 s 3
1 s 1
1 s 7
1 s 0
1 s 8
1 s 8
2 f 3 19
2 f 1
2 f 5
2 f 6
2 f 4
3 h 0 17
3 h 8
3 h 9
4 u 6 31
4 u 7
4 u 8
4 u 10

[JOHN H. DAVIS]

Maybe:

Sub mikesmoua()
Dim i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With
For i = Range("A" & Rows.Count).End(3).row To 1 Step -1
    If Cells(i, "A") <> Cells(i + 1, "A") Then Rows(i + 1).Insert
Next i
For Each numrange In Columns(2).SpecialCells(xlConstants, xlNumbers).Areas
        sumaddr = numrange.Address(False, False)
        With numrange.Offset(numrange.Count, 1).Resize(1, 1)
            .Formula = "=SUM(" & sumaddr & ")"
            .Value = .Value
            .Cut numrange.Cells(1, 1).Offset(, 1)
        End With
        c = numrange.Count
Next numrange
Range("A2:A" & Range("A" & Rows.Count).End(3).row).SpecialCells(4).EntireRow.Delete
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With
End Sub

[mikesmoua]

Does not seem to work.

[JOHN H. DAVIS]

The code assumes your starting values are in Columns A and B. Are they?

[JOHN H. DAVIS]

Or?

Sub mikesmoua()
Dim i As Long, y As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With
Columns(1).Insert
For i = Range("B" & Rows.Count).End(3).row To 1 Step -1
    If Cells(i, "B") <> Cells(i + 1, "B") Then Rows(i + 1).Insert
Next i
y = 1
For Each numrange In Columns(3).SpecialCells(xlConstants, xlNumbers).Areas
        sumaddr = numrange.Address(False, False)
        With numrange.Offset(numrange.Count, 1).Resize(1, 1)
            .Formula = "=SUM(" & sumaddr & ")"
            .Value = .Value
            .Cut numrange.Cells(1, 1).Offset(, 1)
        End With
        c = numrange.Count
        Range(sumaddr).Offset(, -2).Value = y
        y = y + 1
Next numrange
Range("B2:B" & Range("B" & Rows.Count).End(3).row).SpecialCells(4).EntireRow.Delete
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With
End Sub

[Glenn Kennedy]

Is your starting point (e.g. s 2) all in a single cell? Is your desired output (eg 1 s 2 29) also ina single cell? If not, please post an Excel sheet showing what you DO want.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[mikesmoua]

Okay, So the code did work. I think I can go off of this and tweak it. Thanks.

I did try to upload a spread sheet but there may be something blocking it from uploading. Somewhat of a secure environment that I am in.

Thanks again.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. If you are satisfied with the solutions provided please mark this thread as SOLVED.

[mikesmoua]

How would I change this code if the column that I want to add is in column I. The total column to be in D. The column that determines the first number column is dependent on column C.

I thought I would be able to tweak it but after multiple times of testing and teaking the code, I am still not able to figure it out. Thanks.