Backfill of Data Based on Subtotals with if Condition

[rja3983]

I am looking to automate the backfilling of data into lots for an inventory count sheet template. Basically, I need to take the physical count quantites from Col F and distribute them across the lots in Column D for each item. The results are shown in Col G. It seems like an if condition to see if the value in F matches subtotal in E.

Inventory is tracked by Items(Col B) and Lot(Col D). We are looking to update the system information based on a physical count with the #’s indicate in Column E. If the subtotal for each item # in Colum E matches the total for that item in Column F, I need the information in Column E to carry over to Column G

If the quantity in Column E is greater than qty in Col F, the differene needs to be subtracted from the oldest(1^st) lot for that item in Column G. If the difference is greater than the quantity subtracted from the first lot, I would want to look to the next oldest lot to subtract the difference.

If the quantity in Column E is less than the qty in Col F, the difference beeds to be added to the newest(last) lot for that item in Column G
Column G are the #s to be imported to the inventory database.

[TABLE="width: 1335"]
<tbody>[TR]
[TD]Id (casesafe)[/TD]
[TD]Item Code[/TD]
[TD]Item Description[/TD]
[TD]Lot[/TD]
[TD]System QOH[/TD]
[TD]Physical Count[/TD]
[TD]Updated Lot QTS[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQZQAY[/TD]
[TD]1171001[/TD]
[TD]1.00K OHM 0603 SMT 1%, ERJ-3EK[/TD]
[TD]1171001 - 7/7/2011 - Sage[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5200[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQYQAY[/TD]
[TD]1171001[/TD]
[TD]1.00K OHM 0603 SMT 1%, ERJ-3EK[/TD]
[TD]1171001 - 12/31/2012 - Sage[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1171001 Total[/TD]
[TD]2342002[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQcQAI[/TD]
[TD]1171743[/TD]
[TD]174K Ohm (0603 SMT 1%)[/TD]
[TD]1171743 - 7/3/2007 - Sage[/TD]
[TD="align: right"]2808[/TD]
[TD="align: right"]2900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQbQAI[/TD]
[TD]1171743[/TD]
[TD]174K Ohm (0603 SMT 1%)[/TD]
[TD]1171743 - 10/31/2009 - Sage[/TD]
[TD="align: right"]114[/TD]
[TD][/TD]
[TD="align: right"]907[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQaQAI[/TD]
[TD]1171743[/TD]
[TD]174K Ohm (0603 SMT 1%)[/TD]
[TD]1171743 - 12/31/2012 - Sage[/TD]
[TD="align: right"]793[/TD]
[TD][/TD]
[TD="align: right"]2808[/TD]
[/TR]
[TR]
[TD]1171743 Total[/TD]
[TD]3515229[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3715[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQgQAI[/TD]
[TD]1581256[/TD]
[TD]SL19 DDR400-512Meg, Generic[/TD]
[TD]1581256 - 3/4/2013 - Sage[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQfQAI[/TD]
[TD]1581256[/TD]
[TD]SL19 DDR400-512Meg, Generic[/TD]
[TD]1581256 - 12/31/2013 - Sage[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQeQAI[/TD]
[TD]1581256[/TD]
[TD]SL19 DDR400-512Meg, Generic[/TD]
[TD]1581256 - 12/31/2014 - Sage[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQdQAI[/TD]
[TD]1581256[/TD]
[TD]SL19 DDR400-512Meg, Generic[/TD]
[TD]1581256 - 12/31/2015 - Sage[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]1581256 Total[/TD]
[TD]6325024[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQhQAI[/TD]
[TD]1581258[/TD]
[TD]DMB SODIMM DDR3 2GB[/TD]
[TD]1581258 - 12/22/2016 - Sage[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]1581258 Total[/TD]
[TD]1581258[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQmQAI[/TD]
[TD]1581458[/TD]
[TD]DMB SODIMM DDR3 4GB[/TD]
[TD]1581458 - 6/8/2015 - Sage[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQlQAI[/TD]
[TD]1581458[/TD]
[TD]DMB SODIMM DDR3 4GB[/TD]
[TD]1581458 - 7/6/2015 - Sage[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]a1g0R0000004bQkQAI[/TD]
[TD]1581458[/TD]
[TD]DMB SODIMM DDR3 4GB[/TD]
[TD]1581458 - 12/31/2015 - Sage[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]1581458 Total[/TD]
[TD]4744374[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]228[/TD]
[TD][/TD]
[TD="align: right"]76[/TD]
[/TR]
</tbody>[/TABLE]