O
OperationsNETTC15
Hi,
I am trying to count unique inventory transactions in order to get a running
value for Quantity on Hand for each item # we stock.
I have a list sorted by date, old to new, but I am comparing item #'s which
are unsorted, in order to find a running tally of QOH(Quantity on Hand). A is
items, B is store #, C is transaction type, D is julian date, E is
transaction qty. I want F to be my running tally.
Col A Col B Col C Col D Col E Col F
40321 5 7 039808 2 (formula I am working
on)
55321 3 T 039808 4
66353 7 F 039809 2
40321 5 T 039820 3
40321 3 9 039821 5
If I sort by item #, my formula is easy, because I'll just add values row by
row, adjacently....but now that my item #'s in column A can be non-adjacent,
I need a condition that says "if item # is 40321, then depending on the
transaction type (+ or -), and store #, then update total quantity on hand.
My formula currently is:
=IF($B2=5, IF(OR($K2="7", $K2="T", $K2="9"), 0+$E2+$F1, 0-$E2+$F1), 0+$F1)
Which works fine, but is not smart enough to distinguish between item #'s...
Right now it will return a running Tally of 5 for $F4, but if I change $A4
to any other item number, it stills says 5 when it should be 2.
I cannot figure this out if the item #'s are unsorted, suggestions?
I am trying to count unique inventory transactions in order to get a running
value for Quantity on Hand for each item # we stock.
I have a list sorted by date, old to new, but I am comparing item #'s which
are unsorted, in order to find a running tally of QOH(Quantity on Hand). A is
items, B is store #, C is transaction type, D is julian date, E is
transaction qty. I want F to be my running tally.
Col A Col B Col C Col D Col E Col F
40321 5 7 039808 2 (formula I am working
on)
55321 3 T 039808 4
66353 7 F 039809 2
40321 5 T 039820 3
40321 3 9 039821 5
If I sort by item #, my formula is easy, because I'll just add values row by
row, adjacently....but now that my item #'s in column A can be non-adjacent,
I need a condition that says "if item # is 40321, then depending on the
transaction type (+ or -), and store #, then update total quantity on hand.
My formula currently is:
=IF($B2=5, IF(OR($K2="7", $K2="T", $K2="9"), 0+$E2+$F1, 0-$E2+$F1), 0+$F1)
Which works fine, but is not smart enough to distinguish between item #'s...
Right now it will return a running Tally of 5 for $F4, but if I change $A4
to any other item number, it stills says 5 when it should be 2.
I cannot figure this out if the item #'s are unsorted, suggestions?