Excel Formula for checking next closest date/year in a raw data

[amanphilip]

Hello to All!

Hope you are having a great day! I am trying to figure out on how to create a checker column where in it will 2 columns. The logic i am trying to achieve is to check Contract # then check the next closest year on Year column and put 1 on it.

Here's the sample screenshot of what i am trying to achieve:

Final Output needed on the report excluding the checker column:

I have searched the internet but i cannot find any thread has the same with the conditions i needed.

Thank you so much in advance. Regards.

 

[awoohaw]

You can try this:

Book1
	A	B	C	D	E
1	CONTRACT ID	Year
2	5465465465	2025		5465465465	2025
3	5465465465	2026		88879813	2027
4	5465465465	2027
5	5465465465	2028
6	5465465465	2029
7	5465465465	2030
8	88879813	2027
9	88879813	2028
10	88879813	2029
11	88879813	2030
12	88879813	2031
Sheet5

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(A2:A12)
E2:E3	E2	=LET(Cntrs,A2:A12,Yrs,$B$2:$B$12,Cntr,D2,Yr,D3,myr,MINIFS(Yrs,Cntrs,Cntr),myr)
Dynamic array formulas.

 

[amanphilip]

Hi awoohaw,

Thank you for your quick reply. My apologies as i forgot to mention that my excel is 2019 and doesn't support dynamic array formulas. Do you think this is possible via vba?

 

[awoohaw]

try this. Its pretty clunky, but it seems to work.

Book1
	A	B	C	D	E
1	CONTRACT ID	Year
2	5465465465	2025		88879813	2027
3	5465465465	2026		5465465465	2025
4	5465465465	2027
5	5465465465	2028
6	5465465465	2029
7	5465465465	2030
8	88879813	2027
9	88879813	2028
10	88879813	2029
11	88879813	2030
12	88879813	2031
Sheet5

Cell Formulas
Range	Formula
D2:E3	D2	= IF(ROWS($A$2:$A2)>SUM(--(IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)),"", INDEX($A$2:$B$12,LARGE((IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)*(ROW($A$2:$A$12)-ROW($A$1)),ROWS($A$2:$A2)),0))
E7:E12	E7	= IF(ROWS($A$2:A7)>SUM((IFERROR(($A$2:$A$12)/($A$1:$A$11),0)<>1)*1),"", INDEX($B$2:$B$11,LARGE((IFERROR($A$2:$A$12/$A$1:$A$11,0)<>1)*(ROW($A$2:$A$12)-ROW($A$1)), SUM((IFERROR(($A$2:$A$12)/($A$1:$A$11),0)<>1)*1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[amanphilip]

Hi awoohaw,

I tried to apply the formulas, the first column is fine however the 2nd one is showing same results. Sorry i am not familiar with array formulas, did i miss something?

And also can you help me tweak the formula to choose the next closest year based on today's date/ local date of the computer?

 

[awoohaw]

Well, intead of trying to capture both columns in one formula, try to break it up and get one at a time.
Again, use CNTL-SHFT_ENTR (CSE) to commit the formula if it doesn't work without it. (It may work in 2019)

Also, enter the whole mini worksheet I have below on a brand new worksheet before you put it in your workbook and alter it.
This is to be sure that the formula works as expected with the data as I've set it up in your environment.

1. Paste into fresh worksheet.
NOTE: you may need to edit the braces out of the formulas in D2 and E2, and then recommit the formula with CSE.
2. Confirm this works, add new data as you see fit in columns A and B. Drag the formula from D2:E2 down as far as necessary.
3. When satisfied with the formula, put it in your worksheet.

Book1
	A	B	C	D	E
1	CONTRACT ID	Year
2	5465465465	2025		88879813	2027
3	5465465465	2026		5465465465	2025
4	5465465465	2027
5	5465465465	2028
6	5465465465	2029
7	5465465465	2030
8	88879813	2027
9	88879813	2028
10	88879813	2029
11	88879813	2030
12	88879813	2031
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	= IF(ROWS($A$2:$A2)>SUM(--(IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)),"", INDEX($A$2:$B$12,LARGE((IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)*(ROW($A$2:$A$12)-ROW($A$1)),ROWS($A$2:$A2)),1))
E2:E12	E2	= IF(ROWS($A$2:$A2)>SUM(--(IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)),"", INDEX($A$2:$B$12,LARGE((IFERROR( ($A$2:$A$12)/($A$1:$A$11),0 )<>1)*(ROW($A$2:$A$12)-ROW($A$1)),ROWS($A$2:$A2)),2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[amanphilip]

Thank you awoohaw. I will try this.