Macro to total values based on vendor criteria, delete rows & continue on

**Tomtherom** · 09-16-2015, 01:32 PM

Good morning everyone
I'm new to the forums and try to learn macros and VBA.

I'm currently involved in a project that takes a lot of my time.

Here is the raw data

REX OIL CO INC 8,486.79
REX OIL CO INC 21,998.20
REX OIL CO INC 63.44
RUSH MEDIUM DUTY TRUCK C 97.14
SAFETY-KLEEN SYSTEMS, IN 374.39
SHIPP CORPORATION NO 2 I 4,778.00
SHIRLEY SEPTIC PUMPING I 110.00
SHIRLEY SEPTIC PUMPING I 78.00
SHIRLEY SEPTIC PUMPING I 110.00
SILVERHEELS TOWING & REC 120.00
SOUTH PARK TRUE VALUE IN 37.46
SOUTH PARK TRUE VALUE IN 3.98
SOUTH PARK TRUE VALUE IN 38.59
SOUTH PARK TRUE VALUE IN 5.58
SOUTH PARK TRUE VALUE IN 63.97
SOUTH PARK TRUE VALUE IN 58.98
SOUTH PARK TRUE VALUE IN 13.99

Question: Is there a macro that would total the amounts for each vendor and then delete the duplicate vendor rows?
This is the outcome after I do it manually

REX OIL CO INC 30,548.43
RUSH MEDIUM DUTY TRUCK C 97.14
SAFETY-KLEEN SYSTEMS, IN 374.39
SHIPP CORPORATION NO 2 I 4,778.00
SHIRLEY SEPTIC PUMPING I 298.00
SILVERHEELS TOWING & REC 120.00
SOUTH PARK TRUE VALUE IN 222.55

Thank you in advance!

**daffodil11** · 09-16-2015, 02:04 PM

I used this:

Sub Versive()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

For r = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Not dict.exists(Trim(Cells(r, 1).Value)) Then
           dict.Add Trim(Cells(r, 1).Value), CSng(Trim(Cells(r, 2)))
    Else: dict(Trim(Cells(r, 1).Value)) = dict(Trim(Cells(r, 1).Value)) + CSng(Trim(Cells(r, 2)))
    End If
Next

'Output in F1:G1 and down
For r = 0 To dict.Count - 1
    Cells(r + 1, 6) = dict.Keys()(r)
    Cells(r + 1, 7) = dict.Items()(r)
Next
Set dict = Nothing

End Sub

This assumes the data is in two columns. Does the raw data come out that way, or is it single column only?

**Tomtherom** · 09-16-2015, 04:09 PM

Hi Daffodil11

there are 3 columns

A is Vendor name
B is Amount
C is description of an account (i did not post it here)

thank you!

**daffodil11** · 09-16-2015, 04:33 PM

Another way would be to simply copy A, paste somewhere (f?), Data->Remove Duplicates.

And then apply a fomula to (g?) =SUMPRODUCT(($A$1:$A$xx=G1)*($B$1:$B$xx)) and copy down

**Tomtherom** · 09-16-2015, 04:52 PM

Will give it a try, thank you kindly.

Macro to total values based on vendor criteria, delete rows & continue on

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to total values based on vendor criteria, delete rows & continue on

Re: Macro to total values based on vendor criteria, delete rows & continue on

Re: Macro to total values based on vendor criteria, delete rows & continue on

Re: Macro to total values based on vendor criteria, delete rows & continue on

Re: Macro to total values based on vendor criteria, delete rows & continue on

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to delete rows based off criteria?

Multi Function Macro (copy/paste values based on criteria, delete/insert rows, printing)

Macro to delete rows based on two different criteria matching

[SOLVED] Macro to delete rows based on multiple criteria

Macro to delete rows based on criteria in 2 maybe 3 fields

macro - delete rows based on multiple criteria.

How can I delete rows based on specific criteria using a macro?

Bookmarks

Bookmarks

Posting Permissions