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.
 
Sort by date Sort by votes
veyselemre said:
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
Click to expand...
thanks for this veyselemre, could you explain how this works please? i'd like to follow it when reading through the code and see where it looks to determine which lines to keep and which to delete.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Your data was thought to be in history. Thus, the last one of the same "P / N" numbered ones that is required for you has remained. The data in column "B" was compared with the data in the previous row in a backward loop. If it is the same, the top row has been deleted. Thus, control was achieved from the end to the beginning. Translated by Google translate. I don't speak much English, sorry.
 
Upvote 0
veyselemre said:
Your data was thought to be in history. Thus, the last one of the same "P / N" numbered ones that is required for you has remained. The data in column "B" was compared with the data in the previous row in a backward loop. If it is the same, the top row has been deleted. Thus, control was achieved from the end to the beginning. Translated by Google translate. I don't speak much English, sorry.
Click to expand...
ahh, that makes sense, yes thank you.

the problem there then being that the P/N could have multiple separate orders placed against it for different customers. Hence using the part number may not be 100% ideal... i can work around that however and use a 'helper' column or similar as the 'marker' to identify individual orders placed.
 
Upvote 0
like this?

Item NameDays Past RDDP/NQty OutstandingDemand NoDemand DateSPCConsignee IDItem ManagerProviders RefEDDRDDSSC CodeProviders Ref DateCreated DateProgression Text
NUT-642 days1383257142644348713CLE33D1G43495OI04/06/2020 12:47PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM
ITSPL SAFETY-644 days202372224275434879GCG95A1K43493OA20/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.
DIE,CRIMPING TOOL-633 days5591556249014349013BPYF1M4406143504OI05/06/2019 07:38Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required.
 
Upvote 0
ops, sorry
here is a correct columns order
Days Past RDDP/NItem NameQty OutstandingDemand NoDemand DateSPCConsignee IDItem ManagerProviders RefEDDRDDSSC CodeProviders Ref DateCreated DateProgression Text
-642 days1383257NUT142644348713CLE33D1G43495OI04/06/2020 12:47PT RESPONSE: ON ORDER WITH SUPPLIER BUT NO DF 04/06/2020 AJC SCM
-644 days2023722ITSPL SAFETY24275434879GCG95A1K43493OA20/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 TOOL249014349013BPYF1M4406143504OI05/06/2019 07:38Demand automatically extended this demand requirement by 60 days. Use Demand Management to cancel Demand if no longer required.
 
Upvote 0
M for post#15
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"Demand Date", type datetime}, {"EDD", type datetime}, {"RDD", type datetime}, {"Created Date", type datetime}}),
    Sort = Table.Sort(DT,{{"Created Date", Order.Descending}}),
    Grp = Table.Group(Sort, {"Item Name"}, {{"Created Date", each List.Max([Created Date]), type datetime}, {"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Grp, "Count", {"Days Past RDD", "P/N", "Qty Outstanding", "Demand No", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}, {"Days Past RDD", "P/N", "Qty Outstanding", "Demand No", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}),
    Desc = Table.Sort(Expand,{{"Created Date", Order.Descending}}),
    Distinct = Table.Distinct(Desc, {"Created Date"}),
    Reorder = Table.ReorderColumns(Distinct,{"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"})
in
    Reorder
 
Upvote 0
exactly, yes... is that done using the 'Demand No' or 'P/N'?

I only ask, as i pointed out to veyselemre, that all items can have multiple orders outstanding, hence i really need to be able to ensure that each order is treated individually.
 
Upvote 0
In this case I used Item Name and Created Date but Item Name AND P/N AND Demand No. can be used also with representative example
for now it was enough to use just Item Name because the rest is the same

for all three columns mentioned above
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"Demand Date", type datetime}, {"EDD", type datetime}, {"RDD", type datetime}, {"Created Date", type datetime}}),
    Sort = Table.Sort(DT,{{"Created Date", Order.Descending}}),
    Grp = Table.Group(Sort, {"Item Name", "P/N", "Demand No"}, {{"Created Date", each List.Max([Created Date]), type datetime}, {"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Grp, "Count", {"Days Past RDD", "Qty Outstanding", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}, {"Days Past RDD", "Qty Outstanding", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}),
    Desc = Table.Sort(Expand,{{"Created Date", Order.Descending}}),
    Distinct = Table.Distinct(Desc, {"Created Date"}),
    Reorder = Table.ReorderColumns(Distinct,{"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"})
in
    Reorder
 
Upvote 0
sandy666 said:
In this case I used Item Name and Created Date but Item Name AND P/N AND Demand No. can be used also with representative example
for now it was enough to use just Item Name because the rest is the same

for all three columns mentioned above
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"Demand Date", type datetime}, {"EDD", type datetime}, {"RDD", type datetime}, {"Created Date", type datetime}}),
    Sort = Table.Sort(DT,{{"Created Date", Order.Descending}}),
    Grp = Table.Group(Sort, {"Item Name", "P/N", "Demand No"}, {{"Created Date", each List.Max([Created Date]), type datetime}, {"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Grp, "Count", {"Days Past RDD", "Qty Outstanding", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}, {"Days Past RDD", "Qty Outstanding", "Demand Date", "SPC", "Consignee ID", "Item Manager", "Providers Ref", "EDD", "RDD", "SSC Code", "Providers Ref Date", "Progression Text"}),
    Desc = Table.Sort(Expand,{{"Created Date", Order.Descending}}),
    Distinct = Table.Distinct(Desc, {"Created Date"}),
    Reorder = Table.ReorderColumns(Distinct,{"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"})
in
    Reorder
Click to expand...
fab, thank you... would the above be possible in VBA code?? for some reason my version of Excel has had the Power Query function disabled by corporate restrictions...
 
Upvote 0
vba is much more dangerous than Power Query which cannot destroy any data
but if you want vba you need to wait for someone else
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,386
Messages
6,119,216
Members
448,876
Latest member
Solitario

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