Find the next start date of same vehicle

[lunar72]

hello everyone,

I have data for various vehicles/trips and i am attempting to show the start date (data i have) and the end date (data that is not provided). I can show this if i can make the connection between the vehicle numbers and populating the start date for the next trip as the end date. unfortunately because of how the information is used it is difficult to maintain the data in descending order. I have tried a few different ways with no success. Any help would be greatly appreciated. I included some sample data and color coded the dates to show the relation between them.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Vehicle[/TD]
[TD]Start Date[/TD]
[TD]End Date (Start of Next Trip)[/TD]
[/TR]
[TR]
[TD]tr-123[/TD]
[TD]1/18/2019[/TD]
[TD]2/3/2019[/TD]
[/TR]
[TR]
[TD]xi-587[/TD]
[TD]2/3/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ru-624[/TD]
[TD]4/8/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tr-123[/TD]
[TD]2/3/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]st-199[/TD]
[TD]1/18/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tr-123[/TD]
[TD]12/13/2018[/TD]
[TD]1/18/2019[/TD]
[/TR]
[TR]
[TD]xi-587[/TD]
[TD]1/18/2019[/TD]
[TD]2/3/2019[/TD]
[/TR]
[TR]
[TD]ru-624[/TD]
[TD]3/4/2019[/TD]
[TD]4/8/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A
​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Vehicle​

[/TD]
[TD]

Start Date​

[/TD]
[TD]

End Date (Start of Next Trip)​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

tr-123​

[/TD]
[TD]

01/18/2019​

[/TD]
[TD]

02/03/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

xi-587​

[/TD]
[TD]

02/03/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

ru-624​

[/TD]
[TD]

04/08/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

tr-123​

[/TD]
[TD]

02/03/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

st-199
​

[/TD]
[TD]

01/18/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

tr-123
​

[/TD]
[TD]

12/13/2018​

[/TD]
[TD]

01/18/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

xi-587​

[/TD]
[TD]

01/18/2019​

[/TD]
[TD]

02/03/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

ru-624​

[/TD]
[TD]

03/04/2019​

[/TD]
[TD]

04/08/2019​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=IFERROR(AGGREGATE(15,6,B$2:B$9/((A$2:A$9=A2)*(B$2:B$9>B2)),1),"")

Hope this helps

M.

 

[DanteAmor]

Another option with array formula:

=IFERROR(SMALL(IF(($A$2:$A$9=A2)*($B$2:$B$9 > B2),($B$2:$B$9)),1),"")

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[lunar72]

Thanks so much for the help gents!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.