Hi All
I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each individual part number, its aggregate total in transit is shown under a calendar month. The delivery schedule is well into 2024. But a few problems I am not able to figure out are that:
(i) Same part number under same order number is being shipped out over multiple shipments, so how do I make an aggregate for these arrivals and put them under separate calendar months according to their ETA?
(ii) The file contains historical data as well i.e parts which have recently been delivered. I am not able to come up with a logic to exclude these delivered parts from the count of incoming inventory.
The source file gets updated maybe couple of times a month and because most of the deliveries are well into the future, projection would not change that much in my report.
I've been trying various combination of vlookup, count, countif, IF statements but unable to come up with any formulae that could achieve the results. Of course a crude way would be to just filter the date based on required parameters, get the figures and put them manually in a calendar based table but I really want to see if some bit of automation can be constructed for this task. Would highly appreciate any assistance.
I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each individual part number, its aggregate total in transit is shown under a calendar month. The delivery schedule is well into 2024. But a few problems I am not able to figure out are that:
(i) Same part number under same order number is being shipped out over multiple shipments, so how do I make an aggregate for these arrivals and put them under separate calendar months according to their ETA?
(ii) The file contains historical data as well i.e parts which have recently been delivered. I am not able to come up with a logic to exclude these delivered parts from the count of incoming inventory.
The source file gets updated maybe couple of times a month and because most of the deliveries are well into the future, projection would not change that much in my report.
I've been trying various combination of vlookup, count, countif, IF statements but unable to come up with any formulae that could achieve the results. Of course a crude way would be to just filter the date based on required parameters, get the figures and put them manually in a calendar based table but I really want to see if some bit of automation can be constructed for this task. Would highly appreciate any assistance.
Cust. Order No. | Qty | Qty In transit | PCB Model | In transit ? | Required ETA |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | 08-Jun-23 | |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | 07-Jun-23 | |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | 15-Jun-23 | |
CZ10462 | 5 | 0 | 12T40S5000-5D.003 | 08-Aug-23 | |
CZ10462 | 5 | 0 | 12T40S5000-5D.003 | 15-Aug-23 | |
CZ10462 | 1 | 1 | 11T40S4000-3D.007 | yes | 11-Oct-23 |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | 22-Aug-23 | |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | 18-Jul-23 | |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | 25-Jul-23 | |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | yes | 22-Sep-23 |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | yes | 09-Oct-23 |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | yes | 13-Oct-23 |
CZ10462 | 5 | 5 | 12T40S5000-5D.003 | yes | 20-Oct-23 |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | yes | 23-Oct-23 |
CZ10462 | 4 | 4 | 12T40S5000-5D.003 | yes | 23-Oct-23 |
CZ10462 | 2 | 2 | 11T40S4000-3D.007 | yes | 25-Oct-23 |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | no | 26-Nov-23 |
CZ10462 | 1 | 0 | 11T40S4000-3D.007 | no | 26-Nov-23 |
CZ10462 | 2 | 0 | 11T40S4000-3D.007 | no | 08-Nov-23 |
CZ10462 | 2 | 0 | 11T40S4000-3D.007 | no | 15-Nov-23 |
CZ10462 | 2 | 0 | 11T40S4000-3D.007 | no | 20-Dec-23 |
CZ10462 | 3 | 0 | 13T40S7000-7D.005 | no | 03-Jan-24 |
CZ10503 | 3 | 0 | 12T40S5000-5D.003 | no | 31-Mar-24 |
CZ10503 | 2 | 0 | 12T40S5000-5D.003 | no | 31-Mar-24 |
CZ10503 | 1 | 0 | 12T40S5000-5D.003 | no | 31-Mar-24 |
CZ10503 | 1 | 0 | 12T40S5000-5D.003 | no | 31-Mar-24 |
CZ100504 | 3 | 0 | 14T40S8000-8D.006 | no | 08-Apr-24 |
CZ100504 | 2 | 0 | 14T40S8000-8D.006 | no | 08-Apr-24 |
CZ100508 | 1 | 0 | 13T40S7000-7D.005 | no | 12-Apr-24 |
CZ100502 | 2 | 0 | 13T40S7000-7D.005 | no | 12-Apr-24 |
CZ100502 | 2 | 0 | 13T40S7000-7D.005 | no | 04-May-24 |
CZ100504 | 3 | 0 | 14T40S8000-8D.006 | no | 28-Apr-24 |
CZ100502 | 2 | 0 | 13T40S7000-7D.005 | no | 04-May-24 |
CZ100507 | 3 | 0 | 12T40S5000-5D.003 | no | 11-May-24 |
CZ100509 | 4 | 0 | 15T40S9000-9D.007 | no | 31-May-24 |
CZ100507 | 1 | 0 | 12T40S5000-5D.003 | no | 05-Jun-24 |
CZ100505 | 2 | 0 | 16T40S4000-4D.001 | no | 15-Jun-24 |
CZ100505 | 2 | 0 | 16T40S4000-4D.001 | no | 15-Jun-24 |
CZ100508 | 3 | 0 | 13T40S7000-7D.005 | no | 10-Jun-24 |
CZ100505 | 4 | 0 | 16T40S4000-4D.001 | no | 06-Jul-24 |
CZ100507 | 6 | 0 | 12T40S5000-5D.003 | no | 13-Jul-24 |
CZ100569 | 3 | 0 | 11T40S4000-3D.007 | no | 31-Jul-24 |
CZ100505 | 2 | 0 | 16T40S4000-4D.001 | no | 03-Aug-24 |
CZ100550 | 3 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100506 | 1 | 0 | 17T40S3000-4D.002 | no | 31-Mar-24 |
CZ100550 | 3 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100550 | 3 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100569 | 3 | 0 | 11T40S4000-3D.007 | no | 01-Oct-24 |
CZ100550 | 2 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100550 | 3 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100550 | 2 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100506 | 1 | 0 | 17T40S3000-4D.002 | no | 31-Mar-24 |
CZ100502 | 4 | 0 | 13T40S7000-7D.005 | no | 02-Nov-24 |
CZ100550 | 4 | 0 | 12T40S5000-5D.003 | no | 31-May-24 |
CZ100506 | 4 | 0 | 17T40S3000-4D.002 | no | 31-Mar-24 |
CZ100506 | 1 | 0 | 17T40S3000-4D.002 | no | 31-Mar-24 |
CZ100563 | 4 | 0 | 17T40S3000-4D.002 | no | 30-Sep-24 |
CZ100563 | 4 | 0 | 17T40S3000-4D.002 | no | 30-Sep-24 |