| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running tally comparing two columns in unsorted list
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
To get the number of unique entries within A2:A10 use the below formula
Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
Oops' please ignore the below post..Suggest you to put headers to your table...
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
Hahah I stumped a Silver Badge! J/K thanks for the replies!
Headers as follows (A-F) HITEM, HSTK, HRX, HDAT, HQTY, Running Tally Regards, OperationsNettc15 "Jacob Skaria" wrote: Oops' please ignore the below post..Suggest you to put headers to your table... -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
FYI, I turned off calculated column so that my top row does not return a
#VALUE. The formula for my top row is slightly different in that I do not add the line above it(since it is the header). "OperationsNETTC15" wrote: Hahah I stumped a Silver Badge! J/K thanks for the replies! Headers as follows (A-F) HITEM, HSTK, HRX, HDAT, HQTY, Running Tally Regards, OperationsNettc15 "Jacob Skaria" wrote: Oops' please ignore the below post..Suggest you to put headers to your table... -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
You should be able to do that with SUMIF() but really sorry; I'm struggling
to understand the entries in Transaction Type column (7,F,T) and when you mention "depending on the transaction type (+ or -)" =SUMIF(A:A,A2,E:E) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: FYI, I turned off calculated column so that my top row does not return a #VALUE. The formula for my top row is slightly different in that I do not add the line above it(since it is the header). "OperationsNETTC15" wrote: Hahah I stumped a Silver Badge! J/K thanks for the replies! Headers as follows (A-F) HITEM, HSTK, HRX, HDAT, HQTY, Running Tally Regards, OperationsNettc15 "Jacob Skaria" wrote: Oops' please ignore the below post..Suggest you to put headers to your table... -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
7 = Inventory Receipt, so add QTY to running tally (+)
T = Transfer from another store , add QTY to running tally (+) F = Transfer to another store, subtract QTY from running tally (-) I tried the SUMIF you gave me, but I suspect it must be nested somehow. "Jacob Skaria" wrote: You should be able to do that with SUMIF() but really sorry; I'm struggling to understand the entries in Transaction Type column (7,F,T) and when you mention "depending on the transaction type (+ or -)" =SUMIF(A:A,A2,E:E) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: FYI, I turned off calculated column so that my top row does not return a #VALUE. The formula for my top row is slightly different in that I do not add the line above it(since it is the header). "OperationsNETTC15" wrote: Hahah I stumped a Silver Badge! J/K thanks for the replies! Headers as follows (A-F) HITEM, HSTK, HRX, HDAT, HQTY, Running Tally Regards, OperationsNettc15 "Jacob Skaria" wrote: Oops' please ignore the below post..Suggest you to put headers to your table... -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running tally comparing two columns in unsorted list
In F2 I have applied the below formula and copied that down...
=SUMPRODUCT(--($A$2:A2=A2),$E$2:E2)-SUMPRODUCT(--($A$2:A2=A2),--($C$2:C2="F"),$E$2:E2) Now what this formula does is count number of items (all types) until that date and deduct the same items under with type "F". Again I am not sure what the type 9 is...I assume that is to be added to the running total. If so the above formula would suit your requirement ; or else adjust to suit...Try and feedback Items Sote# Type Date Qty Running Total 40321 5 7 39808 2 2 55321 3 T 39808 4 4 66353 7 F 39809 2 0 40321 5 T 39820 3 5 40321 3 9 39821 5 10 If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 7 = Inventory Receipt, so add QTY to running tally (+) T = Transfer from another store , add QTY to running tally (+) F = Transfer to another store, subtract QTY from running tally (-) I tried the SUMIF you gave me, but I suspect it must be nested somehow. "Jacob Skaria" wrote: You should be able to do that with SUMIF() but really sorry; I'm struggling to understand the entries in Transaction Type column (7,F,T) and when you mention "depending on the transaction type (+ or -)" =SUMIF(A:A,A2,E:E) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: FYI, I turned off calculated column so that my top row does not return a #VALUE. The formula for my top row is slightly different in that I do not add the line above it(since it is the header). "OperationsNETTC15" wrote: Hahah I stumped a Silver Badge! J/K thanks for the replies! Headers as follows (A-F) HITEM, HSTK, HRX, HDAT, HQTY, Running Tally Regards, OperationsNettc15 "Jacob Skaria" wrote: Oops' please ignore the below post..Suggest you to put headers to your table... -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: To get the number of unique entries within A2:A10 use the below formula Adjust the range to suit your requirement =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) If this post helps click Yes --------------- Jacob Skaria "OperationsNETTC15" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to keep a running tally of boxes filled with info | Excel Worksheet Functions | |||
| How do I find top 3 in an unsorted list? | Excel Worksheet Functions | |||
| formula to keep a running tally of the number of times a certain w | Excel Discussion (Misc queries) | |||
| How Excel 2003 Highlight random cells and running tally appear? | Excel Discussion (Misc queries) | |||
| Comparing and moving unsorted data in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
