Find next instance of name/number in column and return date if matches criteria

jaxo

New Member
Joined
Nov 21, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi, I need a formula to be entered into the Date Out column of example sheet, correct date is posted to the right of table as example. Formula should return a date next to "Opening Stock" or "Move +". It should read the next instance of Paddock number and if the process is Opening Stock" or "Move +, record the date from that row in the Date Out column next to the above "Opening Stock" or "Move +" Would appreciate solutions.

LSR Forrester 2023b.xlsx
E
14
Input
 

Attachments

  • Capture.PNG
    Capture.PNG
    67 KB · Views: 9

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
LSR Forrester 2023b.xlsx
ABCDEFG
2
3DateProcessPaddock/sHerdDate OutDate Out
41/11/2023Opening Stock1Red Cows7/11/2023
517/10/2023Opening Stock6Bont Cows3/11/2023
61/11/2023Opening Stock4Grey Cows7/11/2023
714/10/2023Opening Stock2White Cows3/11/2023
81/11/2023Opening Stock7Weaner Heifers & Bulls3/11/2023
91/11/2023Opening Stock9Sale Herd6/11/2023
101/11/2023Opening StockNot UsedBulls
117/11/2023Move -1Red Cows
127/11/2023Move +3Red Cows9/11/2023
133/11/2023Move -6Bont Cows
143/11/2023Move +8Bont Cows6/11/2023
156/11/2023Move -8Bont Cows
166/11/2023Move +9Bont Cows11/11/2023
177/11/2023Move -4Grey Cows
187/11/2023Move +East Old Lands (L3)Grey Cows
193/11/2023Move -2White Cows
203/11/2023Move +10White Cows6/11/2023
216/11/2023Move -10White Cows
226/11/2023Move +11White Cows9/11/2023
233/11/2023Move -7Weaner Heifers & Bulls
243/11/2023Move +DamWeaner Heifers14/11/2023
253/11/2023Move -7Weaner Heifers & Bulls
263/11/2023Move +15 & L2Weaner Bulls
279/11/2023Move -3Red Cows
289/11/2023Move +2Red Cows10/11/2023
299/11/2023Move -11White Cows
309/11/2023Move +12White Cows14/11/2023
3110/11/2023Move -2Red Cows
3210/11/2023Move +1Red Cows11/11/2023
3310/11/2023Died/Losses -DamWeaner Heifers
3411/11/2023Move -1Red Cows
3511/11/2023Move +5Red Cows14/11/2023
3611/11/2023Move -9Bont Cows
3711/11/2023Move +10Bont Cows14/11/2023
3814/11/2023Move -10Bont Cows
3914/11/2023Move +11Bont Cows17/11/2023
4014/11/2023Move -12White Cows
4114/11/2023Move +13White Cows17/11/2023
4214/11/2023Move -5Red Cows
4314/11/2023Move +6Red Cows17/11/2023
4414/11/2023Move -DamWeaner Heifers
4514/11/2023Move +7Weaner Heifers
4617/11/2023Move -11Bont Cows
4717/11/2023Move +12Bont Cows20/11/2023
4817/11/2023Move -13White Cows
4917/11/2023Move +9White Cows20/11/2023
5017/11/2023Move -6Red Cows
5117/11/2023Move +3Red Cows19/11/2023
5219/11/2023Move -3Red Cows
5319/11/2023Move +2Red Cows20/11/2023
5420/11/2023Move -2Red Cows
5520/11/2023Move +1Red Cows
5620/11/2023Move -12Bont Cows
5720/11/2023Move +8Bont Cows
5820/11/2023Move -9White Cows
5920/11/2023Move +11White Cows
60Total
Input
Cells with Data Validation
CellAllowCriteria
B4:B59List=Process
C4:C59List=Paddock
D4:D59List=Lists!$E$4:$E$12
 
Upvote 0
Formula should return a date next to "Opening Stock" or "Move +". It should read the next instance of Paddock number and if the process is Opening Stock" or "Move +, record the date from that row in the Date Out column next to the above "Opening Stock" or "Move +"
Welcome to the MrExcel board!

  1. Can you explain how you get 6/11/2023 for row 9?
  2. Can you explain why row 10 is empty in the result column?
 
Upvote 0
Thank you Peter

1. Row 9 Should be empty, sorry. Not a good example
2. Row 10 is empty as cattle have not yet been moved out of the "Not Used" Paddock
 
Upvote 0
Thanks. See if this is it.

23 11 22.xlsm
ABCDEFG
1
2
3DateProcessPaddock/sHerdDate OutDate Out
41/11/2023Opening Stock1Red Cows7/11/20237/11/2023
517/10/2023Opening Stock6Bont Cows3/11/20233/11/2023
61/11/2023Opening Stock4Grey Cows7/11/20237/11/2023
714/10/2023Opening Stock2White Cows3/11/20233/11/2023
81/11/2023Opening Stock7Weaner Heifers & Bulls3/11/20233/11/2023
91/11/2023Opening Stock9Sale Herd 6/11/2023
101/11/2023Opening StockNot UsedBulls 
117/11/2023Move -1Red Cows 
127/11/2023Move +3Red Cows9/11/20239/11/2023
133/11/2023Move -6Bont Cows 
143/11/2023Move +8Bont Cows6/11/20236/11/2023
156/11/2023Move -8Bont Cows 
166/11/2023Move +9Bont Cows11/11/202311/11/2023
177/11/2023Move -4Grey Cows 
187/11/2023Move +East Old Lands (L3)Grey Cows 
193/11/2023Move -2White Cows 
203/11/2023Move +10White Cows6/11/20236/11/2023
216/11/2023Move -10White Cows 
226/11/2023Move +11White Cows9/11/20239/11/2023
233/11/2023Move -7Weaner Heifers & Bulls 
243/11/2023Move +DamWeaner Heifers10/11/202314/11/2023
253/11/2023Move -7Weaner Heifers & Bulls 
263/11/2023Move +15 & L2Weaner Bulls 
279/11/2023Move -3Red Cows 
289/11/2023Move +2Red Cows10/11/202310/11/2023
299/11/2023Move -11White Cows 
309/11/2023Move +12White Cows14/11/202314/11/2023
3110/11/2023Move -2Red Cows 
3210/11/2023Move +1Red Cows11/11/202311/11/2023
3310/11/2023Died/Losses -DamWeaner Heifers 
3411/11/2023Move -1Red Cows 
3511/11/2023Move +5Red Cows14/11/202314/11/2023
3611/11/2023Move -9Bont Cows 
3711/11/2023Move +10Bont Cows14/11/202314/11/2023
3814/11/2023Move -10Bont Cows 
3914/11/2023Move +11Bont Cows17/11/202317/11/2023
4014/11/2023Move -12White Cows 
4114/11/2023Move +13White Cows17/11/202317/11/2023
4214/11/2023Move -5Red Cows 
4314/11/2023Move +6Red Cows17/11/202317/11/2023
4414/11/2023Move -DamWeaner Heifers 
4514/11/2023Move +7Weaner Heifers 
4617/11/2023Move -11Bont Cows 
4717/11/2023Move +12Bont Cows20/11/202320/11/2023
4817/11/2023Move -13White Cows 
4917/11/2023Move +9White Cows20/11/202320/11/2023
5017/11/2023Move -6Red Cows 
5117/11/2023Move +3Red Cows19/11/202319/11/2023
5219/11/2023Move -3Red Cows 
5319/11/2023Move +2Red Cows20/11/202320/11/2023
5420/11/2023Move -2Red Cows 
5520/11/2023Move +1Red Cows 
5620/11/2023Move -12Bont Cows 
5720/11/2023Move +8Bont Cows 
5820/11/2023Move -9White Cows 
5920/11/2023Move +11White Cows 
60Total
Stock
Cell Formulas
RangeFormula
E4:E59E4=IF(OR(B4={"Opening Stock","Move +"}),IFERROR(INDEX(FILTER(A5:A$60,D5:D$60=D4),1),""),"")
 
Upvote 0
Solution
Thank you Peter, that looks impressive, however I need to copy back to Excel to check. I am new to this I press the little copy button but when I go to paste in Excel I can only paste as text?
 
Upvote 0
Thank you Peter, I could only paste values but entered your formula directly into my sheet. It worked but when I extended my table to enter some more dates and paddocks, it mentioned some inconsistent column formula. I had some problems with this, is there a way to make it extend automatically when I extend my table?
 
Upvote 0
Just change the two 60 values in the formula to 1000 or any number that will definitely greater than the number of rows that you will ever have and copy the formula down as far as you might ever need.
 
Upvote 0
Just change the two 60 values in the formula to 1000 or any number that will definitely greater than the number of rows that you will ever have and copy the formula down as far as you might ever need.
Thank you very much for your help. If I have follow up questions e.g. I might need to calculate days out of a paddock. If I don't win can I ask you again on this thread? Really appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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