Extract latest entry from duplicates

S

si3po

Board Regular
Joined
Jan 7, 2019
Messages
98
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

Days Past RDDP/NItem NameQty OutstandingDemand NoDemand DateSPCConsignee IDItem ManagerProviders RefEDDRDDSSC CodeProviders Ref DateCreated DateProgression Text
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI22 Jan 19 16:06:31Progression Event - Change of Demand Status.
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI22 Jan 19 16:06:31T204-NA-Not Available - Demand held as Dues Out. 33D1G DES AC
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI5 Feb 19 16:11:34RESPONSE FROM IPT - NO D/F – UNABLE TO LOCATE SUITABLE MANUFACTURER. AWAIT UPDATE. SS 04-2-19
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI4 Jun 19 10:33:13PT HASTENED 04 JUNE 2019........AL
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI6 Jun 19 12:06:37AINU HASTENED A/W REPLY TW
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI21 Nov 19 14:52:06AINU HOLDER HASTENED BY EMAIL - DEMAND REQUIREMENT JUSTIFIED. SS
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI2 Jun 20 08:08:23HASTENER SENT TO DES AS AC-AIRCRAFT1-SCM1A 02/06/2020 AJC SCM
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI4 Jun 20 12:47:10PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA23 Jan 19 08:01:36T204-F1-Future Requirement - Requirement recorded, expect to supply by required delivery date. 95A1K DES TYPHOON
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA8 Feb 19 09:26:44Capability Loss Code is changed from NULL to 'N'
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA8 Feb 19 09:26:44IMPROVEMENTS HAVE BEEN REQUESTED ON AMTS 148625 FDD PRESENTLY 16/01/2020 , AMTS 152936 FDD 30/01/2020
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA10 Apr 19 09:33:09SPOC TO ADVISE ON ANY IMPROVEMENTS TO AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020. SD
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA29 Aug 19 10:17:03REQUIRED FOR ITSPL SAFETY KITS - AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020 @ 11:16 SD
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA20 May 20 14:11:19Externally 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 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI25 Jan 19 10:08:43Estimated Delivery Date is changed from NULL to 08022019
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI25 Jan 19 22:50:48Delivery forecast shown
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI25 Jan 19 22:50:48Progression Event - No Available Parent System Stock.
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI25 Jan 19 22:50:48T204-NA-Not Available - Demand held as Dues Out
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI1 Feb 19 22:36:38Delivery forecast not applicable or not requested
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI1 Feb 19 22:36:38Progression Event - No Available Parent System Stock.
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI1 Feb 19 22:36:38T204-NA-Not Available - Demand held as Dues Out
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI24 Apr 19 08:17:24PT HASTENED 24/04/2019..........AL
-633 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI5 Jun 19 07:38:51Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
is anybody able to assist and point me in the right direction of a solution, or should i give it up as an impossible task that no one here can conquer?

please and thank you in advance.
 
Upvote 0
is that what you want?

Days Past RDDP/NItem NameQty OutstandingDemand NoDemand DateSPCConsignee IDItem ManagerProviders RefEDDRDDSSC CodeProviders Ref DateCreated DateProgression Text
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI22/01/2019 16:06Progression Event - Change of Demand Status.
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI05/02/2019 16:11RESPONSE FROM IPT - NO D/F – UNABLE TO LOCATE SUITABLE MANUFACTURER. AWAIT UPDATE. SS 04-2-19
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI04/06/2019 10:33PT HASTENED 04 JUNE 2019........AL
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI06/06/2019 12:06AINU HASTENED A/W REPLY TW
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI21/11/2019 14:52AINU HOLDER HASTENED BY EMAIL - DEMAND REQUIREMENT JUSTIFIED. SS
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI02/06/2020 08:08HASTENER SENT TO DES AS AC-AIRCRAFT1-SCM1A 02/06/2020 AJC SCM
-642 days1383257NUT1426422/01/201913CLE33D1G30/01/2019OI04/06/2020 12:47PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM
-644 days2023722ITSPL SAFETY2427522/01/20199GCG95A1K28/01/2019OA23/01/2019 08:01T204-F1-Future Requirement - Requirement recorded, expect to supply by required delivery date. 95A1K DES TYPHOON
-644 days2023722ITSPL SAFETY2427522/01/20199GCG95A1K28/01/2019OA08/02/2019 09:26Capability Loss Code is changed from NULL to 'N'
-644 days2023722ITSPL SAFETY2427522/01/20199GCG95A1K28/01/2019OA10/04/2019 09:33SPOC TO ADVISE ON ANY IMPROVEMENTS TO AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020. SD
-644 days2023722ITSPL SAFETY2427522/01/20199GCG95A1K28/01/2019OA29/08/2019 10:17REQUIRED FOR ITSPL SAFETY KITS - AMTS 148625 & AMTS 152936 BOTH HAVE FDD OF JAN 2020 @ 11:16 SD
-644 days2023722ITSPL SAFETY2427522/01/20199GCG95A1K28/01/2019OA20/05/2020 14:11Externally 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 days5591556DIE,CRIMPING TOOL2490125/01/201913BPYF1M18/08/202008/02/2019OI25/01/2019 10:08Estimated Delivery Date is changed from NULL to 08022019
-633 days5591556DIE,CRIMPING TOOL2490125/01/201913BPYF1M18/08/202008/02/2019OI25/01/2019 22:50T204-NA-Not Available - Demand held as Dues Out
-633 days5591556DIE,CRIMPING TOOL2490125/01/201913BPYF1M18/08/202008/02/2019OI01/02/2019 22:36T204-NA-Not Available - Demand held as Dues Out
-633 days5591556DIE,CRIMPING TOOL2490125/01/201913BPYF1M18/08/202008/02/2019OI24/04/2019 08:17PT HASTENED 24/04/2019..........AL
-633 days5591556DIE,CRIMPING TOOL2490125/01/201913BPYF1M18/08/202008/02/2019OI05/06/2019 07:38Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required.
 
Upvote 0
sandy666 said:
is that what you want?
~~~~~ /snip ~~~~~
Click to expand...

Hi Sandy,

not exactly, no sorry... if you look, each demand/order is duplicated (triplicated, quadruplicated...) over multiple rows with only the 'created date' and 'progression text' changing.

What i'm looking to do is delete all but the very latest 'progression text' row for each order so that we only have the very latest information to pass to our customer.

E.g. the first item, Nut, p/n: 1383257 has 7 different progression texts, but i'm only interested in the very latest (made on 04-06-2020) information - we'd like to have that replicated for each individual order.
 
Upvote 0
Ok, so... the table in my first post shows an example of just THREE orders, with its full progression from order placement to latest update. This is done over multiple lines for the same order, each new row containing an updated Progression Text with date/time updated ('Created Date').

I need to take that table, and some how reduce it so that only the newest information (most recent 'Created Date') is kept for each order, and then all other lines are deleted.... therefore, the table in my first post would look like this (below) when all unnecessary lines are removed.

Days Past RDDP/NItem NameQty OutstandingDemand NoDemand DateSPCConsignee IDItem ManagerProviders RefEDDRDDSSC CodeProviders Ref DateCreated DateProgression Text
-642 days1383257NUT1426422 Jan 19 00:00:0013CLE33D1G30 Jan 19 00:00:00OI4 Jun 20 12:47:10PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM
-644 days2023722ITSPL SAFETY2427522 Jan 19 00:00:009GCG95A1K28 Jan 19 00:00:00OA20 May 20 14:11:19Externally 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 days5591556DIE,CRIMPING TOOL2490125 Jan 19 00:00:0013BPYF1M18 Aug 20 00:00:008 Feb 19 00:00:00OI5 Jun 19 07:38:51Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required.
 
Upvote 0
VBA Code: 
Sub TEST()
    Dim i&
    For i = Cells(Rows.Count, 2).End(3).Row To 3 Step -1
        If Cells(i, 2).Value = Cells(i - 1, 2).Value Then
            Rows(i - 1).Delete
        End If
    Next i
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Using VBA/Macros to Duplicate Sheets and Inserting Individual Rows from Table
Replies
2
Views
903
donnington
D

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top