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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,753
Office Version
  1. 2016
Assum A2 down is Failed Del Date, in C2:

Code:
=IFERROR(LOOKUP(2,1/($A$1:A1<A2),$A$1:A1),"No date")
 

Plotmoter

New Member
Joined
Jan 26, 2022
Messages
4
Office Version
  1. 2003 or older
Platform
  1. Windows
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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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),"")
 

Plotmoter

New Member
Joined
Jan 26, 2022
Messages
4
Office Version
  1. 2003 or older
Platform
  1. Windows
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
 

Plotmoter

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

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

 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
1,175,502
Messages
5,897,809
Members
434,677
Latest member
Aurelied

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
Top