Finding next biggest numbers

[toongal12]

I have a bunch of dates with two numbers connected to it. They are not in order and relate to many different items.

I need to be able to output into a different table the next following date and the next two numbers if one or both of the numbers change.


Example: "Current" Data

Item	Date	# One	# Two
Item001	1/4/17	1	1
Item002	1/4/18	1	2
Item001	1/30/17	1	1
Item001	1/4/19	1	2
Item002	1/4/20	3	3

<tbody>
</tbody>

For example, currently Item001 is 1 and 1.

In a different table, If # One and/or # Two changed, I need to display it in a table. If either changed, both numbers will be recorded with the date.

In this case, Item001 changed in the # Two column from 1 to 2 so now it should display: 1/4/19 and 1 and 2


2nd Table: "Next" Data

Item	Next Date	Next # One	Next # Two
Item001	1/4/19	1	2

<tbody>
</tbody>

I am having trouble figuring out what formula to put into each of the three cells (Next Date, Next One, Next Two) that will output even if only one number changes. Sometimes it will result in the next change in both numbers, when I only want the next set.

I was trying to use MAX-IF, but it gives me the highest One and Two, when I just want the next one in order.

 

[jorismoerings]

Couldn't exactly find to what the change should be compared to; so this could be completely wrong solution for your intended use; but perhaps this helps:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G
1	Item	Date	# One	# Two		Ref date	Item001
2	Item001	4-1-2017	1	1		Item	4-1-2017
3	Item002	4-1-2018	1	2		# One	1
4	Item001	30-1-2017	1	1		# Two	1
5	Item001	4-1-2019	1	2
6	Item002	4-1-2020	3	3
7
8	Item001	4-1-2019	1	2
Sheet1

Cell Formulas
Range	Formula
A8	{=INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))}
B8	{=INDEX($B$2:$B$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))}
C8	{=INDEX($C$2:$C$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))}
D8	{=INDEX($D$2:$D$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[toongal12]

The comparison is to find the next number that occurs in the # one column or # two column. It looks like your formula finds when the next date happens, but I need to find when the next change in #One or # two happens The output should be 1/4/19 with 1 and 2 because that is the next time the One or Two columns change.

Couldn't exactly find to what the change should be compared to; so this could be completely wrong solution for your intended use; but perhaps this helps:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Item	Date	# One	# Two		Ref date	Item001
2	Item001	4-1-2017	1	1		Item	4-1-2017
3	Item002	4-1-2018	1	2		# One	1
4	Item001	30-1-2017	1	1		# Two	1
5	Item001	4-1-2019	1	2
6	Item002	4-1-2020	3	3
7
8	Item001	4-1-2019	1	2

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A8 {=INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))} B8 {=INDEX($B$2:$B$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))} C8 {=INDEX($C$2:$C$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))} D8 {=INDEX($D$2:$D$6,MATCH(1,($B$2:$B$6>$G$2)*($A$2:$A$6=$G$1)*(($C$2:$C$6=$G$3+1)+($D$2:$D$6=$G$4+1)),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[jorismoerings]

My formula finds the next occurrence of the item as 4 matches are true:
1. the item must identical; AND
2. the date must be greater then the date the current item has; AND
3. #one is greater then the current item; OR
4. #two is greater than the current item.

Perhaps this view will create clarity:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G
1	Table 1
2	Item	Date	# One	# Two		Ref date	Item001
3	Item001	4-1-2017	1	1		Item	4-1-2017
4	Item002	4-1-2018	1	2		# One	1
5	Item001	30-1-2017	1	1		# Two	1
6	Item001	4-1-2019	1	1
7	Item001	4-1-2021	1	1
8	Item001	5-1-2021	1	1
9	Item001	6-1-2021	2	1
10	Item001	7-1-2021	1	1
11	Item002	4-1-2020	3	3
12
13	Table 2
14	Item	Date	# One	# Two
15	Item001	6-1-2021	2	1
Sheet1

Cell Formulas
Range	Formula
A15	{=INDEX($A$3:$A$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))}
B15	{=INDEX($B$3:$B$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))}
C15	{=INDEX($C$3:$C$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))}
D15	{=INDEX($D$3:$D$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

as shown in Row 8

 

[toongal12]

I apologize, I didn't see Row 8, I thought it was a duplicate entry

When I tried to use it, I receive #N/A.

Also, the numbers always always increments of 1, they are different

My formula finds the next occurrence of the item as 4 matches are true:
1. the item must identical; AND
2. the date must be greater then the date the current item has; AND
3. #one is greater then the current item; OR
4. #two is greater than the current item.

Perhaps this view will create clarity:
Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Table 1
2	Item	Date	# One	# Two		Ref date	Item001
3	Item001	4-1-2017	1	1		Item	4-1-2017
4	Item002	4-1-2018	1	2		# One	1
5	Item001	30-1-2017	1	1		# Two	1
6	Item001	4-1-2019	1	1
7	Item001	4-1-2021	1	1
8	Item001	5-1-2021	1	1
9	Item001	6-1-2021	2	1
10	Item001	7-1-2021	1	1
11	Item002	4-1-2020	3	3
12
13	Table 2
14	Item	Date	# One	# Two
15	Item001	6-1-2021	2	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A15 {=INDEX($A$3:$A$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))} B15 {=INDEX($B$3:$B$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))} C15 {=INDEX($C$3:$C$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))} D15 {=INDEX($D$3:$D$11,MATCH(1,($B$3:$B$11>$G$3)*($A$3:$A$11=$G$2)*(($C$3:$C$11=$G$4+1)+($D$3:$D$11=$G$5+1)),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

as shown in Row 8

 

[FlameRetired]

toongal12,

Do you see jorismoerings' point?

Item002 1/4/18 1 2
Item002 1/4/20 3 3

qualifies according to the rules.

Is there another criteria not mentioned, yet?

 

[toongal12]

toongal12,

Do you see jorismoerings' point?

Item002 1/4/18 1 2
Item002 1/4/20 3 3

qualifies according to the rules.

Is there another criteria not mentioned, yet?

Each item needs a different output on the table

 

[toongal12]

Also, an item can appear many times. There will be a "current" item, but the "current" item can be any date chosen, so it could be in 2017 or it could be in 2013. The output table just has to be able to choose the next set of numbers after the selected one.

I apologize for forgetting to add in this detail.

 

[toongal12]

bump

 

[jorismoerings]

You didn't define what's to consider as the "current" or the selected Item. So compare the table against ......?