Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum quite a bit of duplicates into a cell
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum quite a bit of duplicates into a cell
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum quite a bit of duplicates into a cell
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. -- HTH JonR "bb sprgfld" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum quite a bit of duplicates into a cell
That worked very well, as i put two values in there - a count and then a sum!
Great Solution!!! :) :) THANK YOU!!!! "JonR" wrote: 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. -- HTH JonR "bb sprgfld" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
remove duplicates from one cell that has been combined | Excel Discussion (Misc queries) | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Show duplicates in cell for filter | Excel Discussion (Misc queries) | |||
Show unique data from a row in a cell without duplicates | Excel Discussion (Misc queries) |