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.

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

<tbody>
</tbody>