Tried preforming this in SQL and not having any luck, so I created a new column D to insert a formula. I am pulling data from one table in SQL, it will have multiple records for one item code, I need to display the data on just the items codes with a warehouscode (I) of 002. However, I need the QuantityOnPurchaseOrder value from the records with warehousecode of 009 to display on the row with warehousecode of 002. Thinking I could display this value in the new column and then hide any lines with the warehousecode of 009, that way just 002 is being displayed.
Sample Data
Desired Output and I will hide the QuantityOnPurchaseOrderColumn and have to change my formula at the end to give me correct values in J
Sample Data
WPF_REORDER_PT.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ItemCode | QuantityOnHand | QuantityOnSalesOrder | QuantityOnPO | QuantityOnPurchaseOrder | ReorderPointQty | MinimumOrderQty | LastPhysicalCountDate | WarehouseCode | Recomended Order QTY | ||
2 | WPF01-0648-BLACK | 81 | 10 | 0 | 25 | 150 | 6/23/2023 0:00 | 002 | 150 | |||
3 | WPF01-0648-BLACK | 0 | 10 | 10 | 25 | 150 | 6/23/2023 0:00 | 009 | 150 | |||
4 | WPF01-112-HGB-A | 208 | 20 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | ||||
5 | WPF01-112-NW-A | 64 | 20 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | 300 | |||
6 | WPF01-112-NW-A | 0 | 500 | 500 | 48 | 300 | ####################### | 009 | ||||
7 | WPF01-1GAL | 58 | 32 | 0 | 9 | 27 | 8/17/2023 0:00 | 002 | ||||
8 | WPF01-30296-WHITE | 249 | 0 | 0 | 24 | 250 | 6/23/2023 0:00 | 002 | ||||
9 | WPF01-315M-MOSSY OAK BREAKUP | 86 | 45 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | 300 | |||
10 | WPF01-3315-NAVY | 460 | 0 | 0 | 50 | 300 | 6/23/2023 0:00 | 002 | ||||
11 | WPF01-7301-CAVIAR BLACK-S | 3 | 0 | 0 | 6 | 3 | 6/22/2023 0:00 | 002 | 3 | |||
12 | WPF01-7301-CAVIAR BLACK-S | 0 | 4 | 6 | 6 | 3 | ####################### | 009 | ||||
WPF_REORDER_PT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J3:J12 | J3 | =IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",IF([@QuantityOnHand]-[@QuantityOnSalesOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")) |
Desired Output and I will hide the QuantityOnPurchaseOrderColumn and have to change my formula at the end to give me correct values in J
WPF_REORDER_PT.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ItemCode | QuantityOnHand | QuantityOnSalesOrder | QuantityOnPO | QuantityOnPurchaseOrder | ReorderPointQty | MinimumOrderQty | LastPhysicalCountDate | WarehouseCode | Recomended Order QTY | ||
2 | WPF01-0648-BLACK | 81 | 10 | 10 | 0 | 25 | 150 | 6/23/2023 0:00 | 002 | 150 | ||
4 | WPF01-112-HGB-A | 208 | 20 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | ||||
5 | WPF01-112-NW-A | 64 | 20 | 500 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | 300 | ||
7 | WPF01-1GAL | 58 | 32 | 0 | 9 | 27 | 8/17/2023 0:00 | 002 | ||||
8 | WPF01-30296-WHITE | 249 | 0 | 0 | 24 | 250 | 6/23/2023 0:00 | 002 | ||||
9 | WPF01-315M-MOSSY OAK BREAKUP | 86 | 45 | 0 | 48 | 300 | 6/23/2023 0:00 | 002 | 300 | |||
10 | WPF01-3315-NAVY | 460 | 0 | 0 | 50 | 300 | 6/23/2023 0:00 | 002 | ||||
11 | WPF01-7301-CAVIAR BLACK-S | 3 | 0 | 6 | 0 | 6 | 3 | 6/22/2023 0:00 | 002 | 3 | ||
WPF_REORDER_PT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J4:J5,J7:J11 | J4 | =IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",IF([@QuantityOnHand]-[@QuantityOnSalesOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")) |