hi all,
I think i need some assistance with an Excel VBA script.
Here's what i'm trying to achieve - we employ an ordering system that can export out to an .xlsx file all outstanding orders and all hastening/progression updates for that order. Unfortunately, each update for that demand (of which there can be any number of) is placed into a new row in the export file meaning that each demand can show multiple lines, each with a different progression/update. For our purposes, we need to extract only the most recent update/progression row and have this information placed into an existing outstanding orders spreadsheet.
I have existing VBA code to help me with taking the information and moving it into my existing sheet, however i haven't worked out yet how to whittle down each row so that only the row with the latest progression/update text is present - to make things worse, there will be be a strong likelihood that there are duplicates and lines with no progression present at all.
Here is a very short extract from my export sheet with the first couple of orders:
I think i need some assistance with an Excel VBA script.
Here's what i'm trying to achieve - we employ an ordering system that can export out to an .xlsx file all outstanding orders and all hastening/progression updates for that order. Unfortunately, each update for that demand (of which there can be any number of) is placed into a new row in the export file meaning that each demand can show multiple lines, each with a different progression/update. For our purposes, we need to extract only the most recent update/progression row and have this information placed into an existing outstanding orders spreadsheet.
I have existing VBA code to help me with taking the information and moving it into my existing sheet, however i haven't worked out yet how to whittle down each row so that only the row with the latest progression/update text is present - to make things worse, there will be be a strong likelihood that there are duplicates and lines with no progression present at all.
Here is a very short extract from my export sheet with the first couple of orders:
| Days Past RDD | P/N | Item Name | Qty Outstanding | Demand No | Demand Date | SPC | Consignee ID | Item Manager | Providers Ref | EDD | RDD | SSC Code | Providers Ref Date | Created Date | Progression Text |
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 22 Jan 19 16:06:31 | Progression Event - Change of Demand Status. | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 22 Jan 19 16:06:31 | T204-NA-Not Available - Demand held as Dues Out. 33D1G DES AC | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 5 Feb 19 16:11:34 | RESPONSE FROM IPT - NO D/F – UNABLE TO LOCATE SUITABLE MANUFACTURER. AWAIT UPDATE. SS 04-2-19 | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 4 Jun 19 10:33:13 | PT HASTENED 04 JUNE 2019........AL | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 6 Jun 19 12:06:37 | AINU HASTENED A/W REPLY TW | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 21 Nov 19 14:52:06 | AINU HOLDER HASTENED BY EMAIL - DEMAND REQUIREMENT JUSTIFIED. SS | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 2 Jun 20 08:08:23 | HASTENER SENT TO DES AS AC-AIRCRAFT1-SCM1A 02/06/2020 AJC SCM | |||
| -642 days | 1383257 | NUT | 1 | 4264 | 22 Jan 19 00:00:00 | 13 | CLE | 33D1G | 30 Jan 19 00:00:00 | OI | 4 Jun 20 12:47:10 | PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 23 Jan 19 08:01:36 | T204-F1-Future Requirement - Requirement recorded, expect to supply by required delivery date. 95A1K DES TYPHOON | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 8 Feb 19 09:26:44 | Capability Loss Code is changed from NULL to 'N' | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 8 Feb 19 09:26:44 | IMPROVEMENTS HAVE BEEN REQUESTED ON AMTS 148625 FDD PRESENTLY 16/01/2020 , AMTS 152936 FDD 30/01/2020 | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 10 Apr 19 09:33:09 | SPOC TO ADVISE ON ANY IMPROVEMENTS TO AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020. SD | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 29 Aug 19 10:17:03 | REQUIRED FOR ITSPL SAFETY KITS - AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020 @ 11:16 SD | |||
| -644 days | 2023722 | ITSPL SAFETY | 2 | 4275 | 22 Jan 19 00:00:00 | 9 | GCG | 95A1K | 28 Jan 19 00:00:00 | OA | 20 May 20 14:11:19 | Externally Created Progression Event-20 MAY 20 1411:19-###############-01526 #######-ALL DEMANDS FOR NON AIRCRAFT INVENTORY REPLACEMENT DUE TO WEAR AND TEAR ARE TO BE EMAILED TO ################# WITH FULLY JUSTIFICATION. EACH AIRCRAFT ARRIVES WITH A SET FROM PRODUCTION. | |||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 25 Jan 19 10:08:43 | Estimated Delivery Date is changed from NULL to 08022019 | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 25 Jan 19 22:50:48 | Delivery forecast shown | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 25 Jan 19 22:50:48 | Progression Event - No Available Parent System Stock. | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 25 Jan 19 22:50:48 | T204-NA-Not Available - Demand held as Dues Out | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 1 Feb 19 22:36:38 | Delivery forecast not applicable or not requested | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 1 Feb 19 22:36:38 | Progression Event - No Available Parent System Stock. | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 1 Feb 19 22:36:38 | T204-NA-Not Available - Demand held as Dues Out | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 24 Apr 19 08:17:24 | PT HASTENED 24/04/2019..........AL | ||
| -633 days | 5591556 | DIE,CRIMPING TOOL | 2 | 4901 | 25 Jan 19 00:00:00 | 13 | BPY | F1M | 18 Aug 20 00:00:00 | 8 Feb 19 00:00:00 | OI | 5 Jun 19 07:38:51 | Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required. |
