Find part and add values

[hutch@edge.net]

Greetings again, Gurus.

I don't know if this is even possible via VBA, but thought I'd throw it out there and see what you guys think.

In the attached workbook column D contains fields for the UsagePN. If a part is "rolled-up" in our order system to a new part number, then the OLD part number, (in colmn B), is associated with a UsagePN. What I need to do is, for every part that has a UsagePN in Column D, I need to find that part number in column B and add the inventory values from the old part number to the new one.

For Example:
Row 74 in the attachment has a UsagePN of AC1230105V. I need to take the values from cells G74 thru K74 andd ADD them to the existing values in G77 thru K77, which are the inventory values for AC1230105V.

In other words, since part number AC1230105 has rolled-up to the new part number AC1230105V, I need to add all of the inventory to the new part number, AC1230105V. After this is done for ALL parts with a usage part number, I will delete all the rows containing UsagePN's.

Here's the catch, as you can see in the example, there is no consistency, so a simple formula like adding the values to the same part number with a "V" at the end won't work. I guess you would have to do something like for each row with a value in column D, dim that row so you can refer back to it, then search for a match in column B, insert a "helper" row below where the part number is found to add the quantities, then replace the original with the helper row, and then delete the helper. One of you guys probably knows a better way.

Keep in mind that you won't always find a match, so the macro needs to continue to the next part if no match is found.

I hope I was clear. This one looks like it will be a nightmare, but I hope somebody on here has some ideas.

Thanks in advance for any help you can offer.