Find next bigger date for two criteria (unsorted)

[Bozo the C]

Hello a good day!

Could you please help me finding a formula with which I can lookup the next bigger date for 2 criteria (ID and Start). I want the result in column C (under the C/End).

A,B,C

ID,Start,End
1,01-01-18
1,03-01-18
1,07-01-18
2,01-01-18
2,02-01-18
3,01-01-18
3,04-01-18
3,07-01-18
3,10-01-18

In the example above I would like to lookup the start date from the next row, for the given ID.

Can this be done with a formula in case the rows (for ID) are unsorted?
(sorted will not be a problem)

 

[Special-K99]

This looks like a simple problem but I dont understand it.

Can you supply some output based on your example data?

 

[Bozo the C]

Thanks Special!
At my work I did not want to expiriment much with exporting html.
When home ill paste the table in excel and will try to export it.

With index match you can lookup the max, or the min date for the given ID, what i want is the next bigger date. The date that is greater than the date on the current row.

 

[Special-K99]

Ok, something to consider, what happens if there's only one date, the current one, ie there is no greater date than the one supplied

 

[Bozo the C]

no, thats easy. If error ""

If there is no bigger date, than there is no new line (with a new start date) and there is no end date.

****** http-equiv=Content-Type content="text/html; charset=windows-1252">****** name=ProgId content=Excel.Sheet>****** name=Generator content="Microsoft Excel 15">
******>

                                                                                                       IDStartEnd  11-1-187-1-18this is the max for ID. I don't want the max date, but i  want the first bigger than the start date.  13-1-18  17-1-18  21-1-18  22-1-18  31-1-18  34-1-18  37-1-18  310-1-18

Paste the table with active cel A2 (fieldname ID in A2).
Paste this formula in cel C3: "=INDEX($B$3:$B$11,MATCH(MAX(IF($A$3:$A$11=A3,$B$3:$B$11)),IF($A$3:$A$11=A3,$B$3:$B$11),0))" Enter with Shift + Ctrl + Enter
<link rel="File-List" href="Map1_bestanden/filelist.xml"><style id="Map1_4168_Styles">
<!--table
{mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:"\.";}
.xl154168
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl634168
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:"Short Date";
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl644168
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:"m\/d\/yy\;\@";
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl654168
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
-->
</style>

 

[Aladin Akyurek]

@ Bozo the C

Row\Col	A​	B​	C​
1​	ID	Start
2​	1​	01-01-18​	03-01-18​
3​	1​	03-01-18​	07-01-18​
4​	1​	07-01-18​	07-01-18​
5​	2​	01-01-18​	02-01-18​
6​	2​	02-01-18​	02-01-18​
7​	3​	01-01-18​	04-01-18​
8​	3​	04-01-18​	07-01-18​
9​	3​	07-01-18​	10-01-18​
10​	3​	10-01-18​	10-01-18​

In C2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($A2:$A$10,$A2)>1,MIN(IF($A3:$A$10=$A2,IF($B3:$B$10>$B2,$B3:$B$10))),$B2)

Is this what you are after?

 

[Bozo the C]

This is what I am after for sure Aladin.
I appreciate this very much.
Thanks a lot mister!

Greetings!

 

[Aladin Akyurek]

This is what I am after for sure Aladin.
I appreciate this very much.
Thanks a lot mister!

Greetings!

Glad to hear that. Thanks for the kind update.

 

[Bozo the C]

Oops, sorry, I don't want to seem unthankful. But the formula does not work in case the start dates are unsorted.

I did not see that yesterday, that the formula for most part is only looking down.
I am going to struggle with it. Hopefully i can give it the adjusment it needs. I'll post it when succeeded. First attemts only give #N/A's, or zero's (for date).

 

[Aladin Akyurek]

Oops, sorry, I don't want to seem unthankful. But the formula does not work in case the start dates are unsorted.

I did not see that yesterday, that the formula for most part is only looking down.
I am going to struggle with it. Hopefully i can give it the adjusment it needs. I'll post it when succeeded. First attemts only give #N/A's, or zero's (for date).

Would you post an unsorted sample along with the expected result?