This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
With the input year from 'Column B' it should find the next consecutive year available in 'Column D'.
'Column E' should be filled with the data available in 'Column C' against the year found in the above step.
Consider below example: Input year is 2012(Column B), the next consecutive year available in Column D is 2014(Year 2013 is missing). And column E is filled with data from Column C available against the year 2014(found in the above step)
I used the R tool to check for the next consecutive year and then the Join Tool to bring respective values.
Check this out:
The R tool code is used to bring the next consecutive year in column D. Using sapply function, I can check a single value of column B against the whole column D, and get the "minimum greater than" value (that means, the next consecutive value).
Then, I used join tool with the original dataset to match respective consecutive year values.
This brings me column E.
The last thing to do is Join that column E to the original dataset again. I used left join so Null values come along (that means, if there's no consecutive year for that record, a Null value should show up).
See workflow and let me know if you have any doubts. Don't forget to change R code if your column names are different.