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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]# One[/TD]
[TD]# Two[/TD]
[/TR]
[TR]
[TD]Item001[/TD]
[TD]1/4/17[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Item002[/TD]
[TD]1/4/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Item001[/TD]
[TD]1/30/17[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Item001[/TD]
[TD]1/4/19[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Item002[/TD]
[TD]1/4/20[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Next Date[/TD]
[TD]Next # One[/TD]
[TD]Next # Two[/TD]
[/TR]
[TR]
[TD]Item001[/TD]
[TD]1/4/19[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[toongal12]

I copied your data to a blank sheet and I can see why it doesn't work for my data.

Your formula only works if the numbers increase by 1, but my numbers can increase by any amount. How would I be able to figure out the next set if they don't increase by 1? In your data set, you have Item002 going from # One 1 to # One 3. Your formula wouldn't be able to find it because it is more than 1 increase

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
Table 1
Item	Date	# One	# Two	Ref date	Item001
Item001	Item
Item002	# One
Item001	# Two
Item001
Item001
Item001
Item001
Item001
Item002
Table 2
Item	Date	# One	# Two
Item001

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4-1-2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4-1-2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4-1-2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30-1-2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4-1-2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]4-1-2021[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]5-1-2021[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]6-1-2021[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]7-1-2021[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4-1-2020[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]6-1-2021[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A15[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D15[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



as shown in Row 8

 

[toongal12]

I apologize, I didn't see how vague it was

The "current" is for each item, and can be any date or time. It is constantly different for the reason of only finding the next version. For an example, you can just take the first instance of Item001 and Item002.

I need to find the next set of numbers for each item

Item001, I need to find the next
Item002, I need to find the next
Item003 I need to find the next
etc


So every item has a "current" and "next" set of numbers

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

 

[toongal12]

The numbers are not always increments of one, they can be any number, but the numbers is always increasing