It seems like Python may be the shortest path to a solution on this one. VBA most likely has the capability according to the responses here and another friend of mine - but it will be a learning curve for me. I am not gifted in Python either, but this would be fairly trivial compared to learning VBA. I was hoping there was some secret function in Excel as there always seems to be, but I guess this is a fairly unique problem.
The only remaining option is a custom ULP for Eagle (the origin of the data). I have rudimentary skills that are enough to get the data that I have although some other BOM scripts are able to group by value like this. I just can't follow the code well enough to figure out how they do it. Anyone have any ULP scripting skills for Eagle? I could scare up some $$ for a solution that works.
Output from Eagle: Separate files
TOP SMD/P&P parts - part number (attribute), qty, reference designators
TOP Hand place parts - part number (attribute), qty, reference designators
BOTTOM SMD/P&P parts - part number (attribute), qty, reference designators
BOTTOM Hand place parts - part number (attribute), qty, reference designators
WHOLE PCB All parts - part number (attribute), qty, reference designators
That data can be read by existing spreadsheets, assembly, and purchasing software to create the various docs I need. Doing all this manually is not too bad when you do a couple of simple PCB's. I am now at the point where I am dealing with about 14 different designs and over 300 unique parts and still a 1 man shop. I have to design, order, inventory, assemble, test, etc.....the whole enchilada and it is a MESS!