Solved

# Tough excel math formula

Posted on 2011-03-19

This should be simple, but it’s driving me nuts…

I have a spreadsheet that looks like this:

Part Number Lot Number Quantity

020-044 Goods 8

020-044 CAH217 -5

020-044 VEG076 1

020-044 Total 4

020-045 Goods 6

020-045 CNS015 -4

020-045 SPG015 -1

020-045 CAH217 -1

020-045 VEG076 1

020-045 Total 1

020-056 Goods 16

020-056 FLX017 -4

020-056 CAS037 -6

020-056 LAS006 -3

020-056 FLP476 -1

020-056 Total 2

What I need is a formula that will go through the spread sheet and copy the total of each part number to the cel next to the “goods” line for each entry. For instance, part #020-044 has a total of 4 units. I simply need that “4” copied to cel D2. The end result should look like this:

Part Number Lot Number Quantity Total

020-044 Goods 8 4

020-044 CAH217 -5

020-044 VEG076 1

020-044 Total 4

020-045 Goods 6 1

020-045 CNS015 -4

020-045 SPG015 -1

020-045 CAH217 -1

020-045 VEG076 1

020-045 Total 1 2

020-056 Goods 16

020-056 FLX017 -4

020-056 CAS037 -6

020-056 LAS006 -3

020-056 FLP476 -1

020-056 Total 2

There are over 50,000 part numbers on this spreadsheet. Some have one or two lot numbers; some have over a dozen, so doing this manually is not an option. I am using MS Excel 2010.