macro to condense data.

[awsumchillicrab]

Hi Excel people.

I've got something like this:
Column A showing grocery items, and they can repeat.
Column B showing the number of that item I need.
Column C showing the number of that item I bought in a transaction.

If I need 10 cucumbers and have bought 7 so far in two transactions...

A2 and A10 may say "cucumber"
B2 and B10 will both be "10"
C2 and C10 may be "3" and "4" respectively.

I'm trying to make a macro that will add 3+4, then replace C2 with "7" and delete the entire row10. So that at the end of the macro, "cucumber" will only ever show up once in column A.

If i buy 2 more cucumbers today, I'd like to add the transaction at the bottom of my table, re-run the macro, and have C2 showing "9".

Any help will be great, thanks!

 

[VoG]

Try this with a copy of your sheet.

Sub test2()
Dim LR As Long, i As Long, s As String
Application.ScreenUpdating = False
s = Range("C1").Value
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("D").Insert
With Range("D2:D" & LR)
    .Formula = "=SUMIF(A:A,A2,C:C)"
    .Value = .Value
End With
For i = LR To 3 Step -1
    If WorksheetFunction.CountIf(Columns("A"), Range("A" & i).Value) > 1 Then Rows(i).Delete
Next i
Columns("C").Delete
Range("C1").Value = s
Application.ScreenUpdating = True
End Sub

 

[awsumchillicrab]

awesome!

I have a question..

Why do you use
For i = LR To 3 Step -1 ?

Thanks

 

[VoG]

I assumed that row 1 contained headers. Therefore you only need to go up as far as row 3 to check for duplicates.

 

[awsumchillicrab]

don't you have to go to row 2?
in case row 2 and 3 are duplicates?

 

[VoG]

No, only to row 3. Tested.