Finding next and previous value of a column relative to a specific ID column

[surendarmani]

Hi All,

I am a novice user learning formulas in excel. I am sure this following task is a no-brainer for all the experts here.

I've been trying to figure this out myself. Unfortunately, I didn't succeed in retrieving the expected outputs.

I have the ID, year, and GPA column in my dataset.

I am required to get 4 new columns: Previous year, previous_year_GPA, next year, next_year_GPA

Previous_year:
This is specific to the Year column.
I want the formula to look for the student ID column and see whether there is a (row-1) column specific to the ID, if yes print that previous row year in the"Previous_Year" column. If in case there is no (row-1) for the specific ID then print "No Previous data".

Previous_year_GPA:
Same as above. The only difference is the GPA column instead of the year column.

Next_year:
Here, we are looking to finding the (row+1)th Year and print the value in the "Next_Year" column. If row+1 data is not available for a specific ID then "No next data"

Next_year_GPA
Same as above but with GPA column instead of year column.

Can you please help with this?

Mr_Excel_Forum_Question_Previous_Next_Year_data_Retrieval.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Actual Dataset							Expected Dataset with next year data (row+1) & previous year data (row-1)
2	ID	Name	Year	GPA				ID	Name	Year	GPA	Next_Year	Next_year_GPA	Previous_year	Previous_year_GPA
3	101	John Smith	2020	3.55				101	John Smith	2020	3.55	2021	3.25	No previous data	No previous data
4	101	John Smith	2021	3.25				101	John Smith	2021	3.25	2022	4	2020	3.55
5	101	John Smith	2022	4				101	John Smith	2022	4	No next data	No next data	2021	3.25
6	156	Robert Terry	2021	2.67				156	Robert Terry	2021	2.67	2022	3.19	No previous data	No previous data
7	156	Robert Terry	2022	3.19				156	Robert Terry	2022	3.19	No next data	No next data	2021	2.67
8	187	Lucas Derek	2020	3.49				187	Lucas Derek	2020	3.49	No next data	No next data	No previous data	No previous data
9	199	Sam Campbell	2022	3.9				199	Sam Campbell	2022	3.9	No next data	No next data	No previous data	No previous data
10								212	William Torres	2020	3.5	2021	3.7	No previous data	No previous data
11								212	William Torres	2021	3.7	No next data	No next data	2020	3.5
12
13
Sheet1

Adding screenshot just in case some issues pop up with the above script.

Thanks
Surendar

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Actual Dataset							Expected Dataset with next year data (row+1) & previous year data (row-1)
2	ID	Name	Year	GPA				ID	Name	Year	GPA	Next_Year	Next_year_GPA	Previous_year	Previous_year_GPA
3	101	John Smith	2020	3.55				101	John Smith	2020	3.55	2021	3.25	No previous data	No previous data
4	101	John Smith	2021	3.25				101	John Smith	2021	3.25	2022	4	2020	3.55
5	101	John Smith	2022	4				101	John Smith	2022	4	No Next Data	No Next Data	2021	3.25
6	156	Robert Terry	2021	2.67				156	Robert Terry	2021	2.67	2022	3.19	No previous data	No previous data
7	156	Robert Terry	2022	3.19				156	Robert Terry	2022	3.19	No Next Data	No Next Data	2021	2.67
8	187	Lucas Derek	2020	3.49				187	Lucas Derek	2020	3.49	No Next Data	No Next Data	No previous data	No previous data
9	199	Sam Campbell	2022	3.9				199	Sam Campbell	2022	3.9	No Next Data	No Next Data	No previous data	No previous data
10								212	William Torres	2020	3.5	No Next Data	No Next Data	No previous data	No previous data
11								212	William Torres	2021	3.7	No Next Data	No Next Data	No previous data	No previous data
List

Cell Formulas
Range	Formula
L3:M11	L3	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$H3,$C:$C,$J3+1)),"No Next Data")
N3:O11	N3	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$H3,$C:$C,$J3-1)),"No previous data")

 

[surendarmani]

It works like a charm, Ser. Thank you so much for helping me out on this one. Now, I am looking forward to learning and growing with this community.

Thanks again!

Surendar

 

[Fluff]

You're welcome & thanks for the feedback.

 

[surendarmani]

Also, one more question. What if I don't have the reference table ("Actual Dataset") and only have the "Expected Dataset with next year data (row+1) & previous year data (row-1)" table as my only table. How do I self-reference the cells in the ID, year and GPA columns to retrieve the value?

Mr_Excel_Forum_Question_Previous_Next_Year_data_Retrieval.xlsx
	H	I	J	K	L	M	N	O
1	Expected Dataset with next year data (row+1) & previous year data (row-1)
2	ID	Name	Year	GPA	Next_Year	Next_year_GPA	Previous_year	Previous_year_GPA
3	101	John Smith	2020	3.55
4	101	John Smith	2021	3.25
5	101	John Smith	2022	4
6	156	Robert Terry	2021	2.67
7	156	Robert Terry	2022	3.19
8	187	Lucas Derek	2020	3.49
9	199	Sam Campbell	2022	3.9
10	212	William Torres	2020	3.5
11	212	William Torres	2021	3.7
Sheet1

Thanks
Surendar

 

[Fluff]

Just change the H3 to A3 & the J3 to C3

 

[surendarmani]

Apologies. I may have confused you! What if I have only one table like this and how would I populate the values in next year, previous year columns. The above formula worked perfectly for 2 tables.

Mr_Excel_Forum_Question_Previous_Next_Year_data_Retrieval.xlsx
	A	B	C	D	E	F	G	H
1	Expected Dataset with next year data (row+1) & previous year data (row-1)
2	ID	Name	Year	GPA	Next_Year	Next_year_GPA	Previous_year	Previous_year_GPA
3	101	John Smith	2020	3.55
4	101	John Smith	2021	3.25
5	101	John Smith	2022	4
6	156	Robert Terry	2021	2.67
7	156	Robert Terry	2022	3.19
8	187	Lucas Derek	2020	3.49
9	199	Sam Campbell	2022	3.9
10	212	William Torres	2020	3.5
11	212	William Torres	2021	3.7
Sheet1

Thanks
Surendar

 

[surendarmani]

I don't think the previous formula is valid for the recent scenario since there is no lookup ID column to validate that the specific year and gpa belonged to that specific ID. Can you please help me with this one?

Thanks
Surendar

 

[Fluff]

Did you try the original formula with the changes I suggested?

 

[surendarmani]

Did you try the original formula with the changes I suggested?

Yes, Ser. I did. The result came up with only "No Previous data" for any cells.