Insertion of 2nd last date occurrence - Excel

Plotmoter

New Member
Joined
Jan 26, 2022
Messages
4
Office Version
  1. 2003 or older
Platform
  1. Windows
Hi all,

Needing to have a formula that will automatically insert the 2nd last date occurrence.

Based on a table with column A being the Failed Delivery date, column B listing the relevant store code and column C being the column pulling the 2nd last date for when store last failed delivery was.

But not savy and after 2 days of searching and trying I cannot make it work at all.

Would really appreciate help.

Many thanks

Capture.JPG
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Assum A2 down is Failed Del Date, in C2:

Code:
=IFERROR(LOOKUP(2,1/($A$1:A1<A2),$A$1:A1),"No date")
 
Upvote 0
Thank you very much - it will pull the first line on this, but when dragging don across all rows it doesn't match. What would the formula be to include a long list of different store numbers and delivery dates instead of just the one line?
Many thanks
 
Upvote 0
Hi,

If I understand correctly, this should work for you:

Book3.xlsx
ABC
1Failed DateStorePrevious Failed Date
28/20/202122 
38/20/202156 
48/20/202175 
58/20/202199 
68/20/2021156 
78/21/20214456 
88/21/2021780 
98/21/2021556 
108/21/2021897 
118/21/20211145 
128/21/2021228/20/2021
138/21/202173 
148/21/202114 
158/21/20219 
168/21/202188 
178/21/202177 
188/23/202121 
198/23/2021758/20/2021
208/24/2021568/20/2021
218/25/202115 
228/25/202117 
238/25/20215568/21/2021
Sheet960
Cell Formulas
RangeFormula
C2:C23C2=IFERROR(LOOKUP(2,1/((A$2:A2<A2)*(B$2:B2=B2)),$A$2:A2),"")
 
Upvote 0
If I use flash fill to drag down it just inserts the date before the current one, as opposed to the actual previous failed delivery date.
Capture2.JPG
 
Upvote 0

jtakw - THANK YOU - THANK YOU - THANK YOU!!! WORKS A TREAT! VERY VERY GRATEFUL!!!​

 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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