how to sum quite a bit of duplicates into a cell

[bb sprgfld]

I have quite a bit of data to review-what i need to do, is sum up any
duplicate vendors as people thru the years have given same vendor names but
spellt differently. The common cell is the address line 1, if it matches the
sum up the total paid, if not a duplicate then leave it alone. Got any ideas
that would work?
Here is a sample of the data. Please keep in mind 4,000 rows. I am having a
hard time formatting this as it is on my spreadsheet. If you are having an
issue with reading it-please email me and i will send you a sample in the
reply. Thanks for your help!!


Vendor Name Address 1 Address 1 Address 2 City State Zip Code Phone
Number Total Amount Paid
4imprint, Inc. P.O. Box 1641 P.O. Box 1641 Milwaukee WI 53201-1641 (920)
236-7272 $362.55
A G Industries P.O. Box 270099 P.O. Box 270099 St. Louis MO 63127 (636)
349-4466 $180.17
A1 CPR & First Aid PO Box 1182 PO Box 1182 Aumsville OR 97325 $3,429.00
A-1 Fire Protection Umpqua Valley Fire Services Umpqua Valley Fire
Services 3773 Main St. Springfield OR 97478 $164.50
A-1 Lock & Safe 242 W. 6th Ave. 242 W. 6th Ave. Eugene OR 97401 (541)
344-3022 $535.00
A-1 Speedy Plumbing, Inc. PO Box 5617 PO Box 5617 Grants
Pass OR 97527 $488.70
AA Professional Cleaning PO Box 302 PO Box 302 Jefferson OR 97352 (541)
327-8223 $825.00
AAAP PO Box 2206 PO Box 2206 Scottsdale AZ 85252-2206 (480) 296-6190 $130.00
AACO A-1 Health Care Services PO Box 75111 PO Box
75111 Seattle WA 98125 (800) 656-4414 $26,293.13
AACO A-1 Health Care Srvcs, Inc. PO Box 75111 PO Box
75111 Seattle WA 98125 (800) 656-4414 $33,888.54
AACO A-1 Health Care Svcs, Inc. PO Box 75111 PO Box
75111 Seattle WA 98125 (800) 656-4414 $50,137.01
AACO A-1 Hlth Care Services, Inc. PO Box 75111 PO Box
75111 Seattle WA 98125 (800) 656-4414 $7,339.19

 

[Sheeloo]

You can send the file to me...
(e-mail address removed)

just remove X and Z
Try this -
Assuming address line 1 is in Col B
then through Data->Filter->Advanced Filter ... Unique Values only... get the
unique list in any Col, say Col Y starting at row 2
then in Z2 enter
=SUMPRODUCT(--(B:B2000=Y2),(F1:F2000))
and copy down...

Change 2000 to the end of your data... F to the Col with amount you want to
sum...

 

[JonR]

Have you tried using a pivot table?

Use the address for rows, vendor name for columns and total amount paid for
the data. This will give you a column for every variation of vendor name
spelling, but if your addresses are consistent it should be fairly easy to
figure out.

Assuming you can edit your data and you really want to go through the
effort, you can sort your data on the address and correct the spelling on the
vendor names. Then your pivot table will display the total for each vendor.

 

[bb sprgfld]

That worked very well, as i put two values in there - a count and then a sum!
Great Solution!!! THANK YOU!!!!