Unusual Request

Each month I need to update a report that consists of about 30,000 lines of data. I've been using "INDEX & MATCH" to perform the apprpriate calculations once I dump the data into a linked worksheet. However, those fine people who send me this info, having been throwing me a few curveballs lately. The data appears as follows:

Acc # Company Units Budget

SP186412 AB COMP 465 $56,258
SP186413 CD COMP 698 $98,558
SP186414 EF COMP 236 $44,478

Ok, simple enough. When I dump in the new data, it will automatically update my numbers, totaling both the Units and Budget column. But lately, the data has looked like this:

Acc # Company Units Budget

SP186412a AB COMP 465 $56,258
SP186412c AB COMP 465 $69,569
SP186412r AB COMP 465 $11,254

SP186413a CD COMP 698 $69,214
SP186413c CD COMP 698 $98,558
SP186413r CD COMP 698 $55,256

SP186414a EF COMP 236 $44,478
SP186414c EF COMP 236 $44,478
SP186414r EF COMP 236 $44,478

The Budget column is not a problem. Each line item can be totaled. However,in the unit column, I only need totaled whichever account # ends in an "a" or an "r". AND I don't need to have a duplicate of each account number. In a perfect world, I would prefer to have it set up the way it was from the beginning. Whoever solves this one will become my new hero!

Thanks

Anthony