How do I remove duplicate item no and sum the Quantity, for example:
Table: Item Ledger Entry
Item no Posting Date Quantity(M3)
A 01/02/23 5
B 01/02/23 8
C 01/02/23 10
___________________________________________
A 02/02/23 7
B 02/02/23 5
C 02/02/23 3
___________________________________________
A 03/02/23 5
B 03/02/23 2
C 03/03/23 4
Result :total A- 17(M3),
B- 15(M3)
C- 17(M3)
NL("Rows") for the item number, then NL("Sum") for the quantity.
Thanks, the NL("sum") works for quantity but not the NL("Rows") for the item no.
For Item no, I use =NL("Rows","Item Ledger Entry","Item No.","Company=",$D$3,"Document No.",I12,"Entry Type","Sale","Document Type","Sales Shipment")
This is the result,
Doc No Item No Posting date Sum Volume(M3)
DO23-03146 A 01/03/23 5
DO23-03149 B 01/03/23 7
DO23-03150 B 01/03/23 7
DO23-03151 B 01/03/23 7
DO23-03152 A 02/03/23 5
DO23-03153 C 02/03/23 8
D023-03154 C 02/03/23 8
A,B,C are the mix code use by production and each delivery order number will have different mix code.
How do I group 1 mix code for the whole month of march? for example, 01/03/23..31/03/23 mix code- 30RNZ-V10 (A) and the sum quantity for march is 5m3?
You may want to work with a consultant on this. Your NL function will return a unique list of Item No. but if you have different item number/mix codes, it is doing what it is supposed to do. From what I understand of your description, Item A is really Item A1, Item A2, Item A3 which are all different from each other.
It could also be that I'm not understanding your data which is where working with a consultant can help.