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)

 

[Bozo the C]

yes, I am going to do that, will cost a few moments. The effect is there after sorting the table (for example your table above) descending for start date

 

[Bozo the C]

	A	B	C
1
2		Ascending
3	ID	Start	End
4	1	01-01-18	03-01-18
5	1	03-01-18	07-01-18
6	1	07-01-18	07-01-18
7	2	01-01-18	02-01-18
8	2	02-01-18	02-01-18
9	3	01-01-18	04-01-18
10	3	04-01-18	07-01-18
11	3	07-01-18	10-01-18
12	3	10-01-18	10-01-18
13
14		Descending
15	ID	Start	End
16	3	10-01-18	01-00-00
17	1	07-01-18	01-00-00
18	3	07-01-18	01-00-00
19	3	04-01-18	01-00-00
20	1	03-01-18	01-00-00
21	2	02-01-18	01-00-00
22	1	01-01-18	01-01-18
23	2	01-01-18	01-01-18
24	3	01-01-18	01-01-18

<tbody>
</tbody>

 

[Bozo the C]

Still not looking great. The HTMLMaker is not going to work on my current...

The formula =IF(COUNTIFS($A4:$A$12,$A4)>1,MIN(IF($A5:$A$12=$A4,IF($B5:$B$12>$B4,$B5:$B$12))),$B4)

Can be placed in cel C4.

Thanks!

 

[Aladin Akyurek]

Still not looking great. The HTMLMaker is not going to work on my current...

The formula =IF(COUNTIFS($A4:$A$12,$A4)>1,MIN(IF($A5:$A$12=$A4,IF($B5:$B$12>$B4,$B5:$B$12))),$B4)

Can be placed in cel C4.

Thanks!

Would you please indicate the expect results, not what the current formula calculates?

 

[Bozo the C]

Well, the result must me the same as the first table. Ordering descending is giving table 2. What I want is table 3. Meaning the formula to work in case the dates are not sorted properly.
​

	A	B	C
1
2		Ascending
3	ID	Start	End
4	1	01-01-18	03-01-18
5	1	03-01-18	07-01-18
6	1	07-01-18	07-01-18
7	2	01-01-18	02-01-18
8	2	02-01-18	02-01-18
9	3	01-01-18	04-01-18
10	3	04-01-18	07-01-18
11	3	07-01-18	10-01-18
12	3	10-01-18	10-01-18
13
14		Descending
15	ID	Start	End
16	3	10-01-18	01-00-00
17	1	07-01-18	01-00-00
18	3	07-01-18	01-00-00
19	3	04-01-18	01-00-00
20	1	03-01-18	01-00-00
21	2	02-01-18	01-00-00
22	1	01-01-18	01-01-18
23	2	01-01-18	01-01-18
24	3	01-01-18	01-01-18
25
26		Must be
27		Descending
28	ID	Start	End
29	3	10-01-18	10-01-18
30	1	07-01-18	07-01-18
31	3	07-01-18	10-01-18
32	3	04-01-18	07-01-18
33	1	03-01-18	07-01-18
34	2	02-01-18	02-01-18
35	1	01-01-18	03-01-18
36	2	01-01-18	02-01-18
37	3	01-01-18	04-01-18

<tbody>
</tbody>

​

 

[Aladin Akyurek]

Are the start dates on the rows 29-37 dd/mm/yy or mm/dd/yy?

 

[Bozo the C]

These are mm/dd/yy. All dates start on the first of each month (in this example).

 

[Aladin Akyurek]

These are mm/dd/yy. All dates start on the first of each month (in this example).

Thanks...

Control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($A$2:$A$10,$A2)>1,IF(SUM(0+ISNUMBER(IF($A$2:$A$10=$A2,IF($B$2:$B$10>$B2,$B$2:$B$10)))),
     MIN(IF($A$2:$A$10=$A2,IF($B$2:$B$10>$B2,$B$2:$B$10))),$B2),$B2)

 

[Bozo the C]

It's working perfect. I am a lucky guy with you having noticed my question.

Thank you very much Aladin!

 

[Aladin Akyurek]

It's working perfect. I am a lucky guy with you having noticed my question.

Thank you very much Aladin!

Glad to help. And thanks for the kind words.