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]

It worked for me.

+Fluff 1.xlsm
	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	2021	3.25	No Next Data	No Next Data
4	101	John Smith	2021	3.25	2022	4	2021	3.25
5	101	John Smith	2022	4	No Next Data	No Next Data	2022	4
6	156	Robert Terry	2021	2.67	2022	3.19	No Next Data	No Next Data
7	156	Robert Terry	2022	3.19	No Next Data	No Next Data	2022	3.19
8	187	Lucas Derek	2020	3.49	No Next Data	No Next Data	No Next Data	No Next Data
9	199	Sam Campbell	2022	3.9	No Next Data	No Next Data	No Next Data	No Next Data
10	212	William Torres	2020	3.5	2021	3.7	No Next Data	No Next Data
11	212	William Torres	2021	3.7	No Next Data	No Next Data	2021	3.7
List

Cell Formulas
Range	Formula
E3:F11	E3	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$A3,$C:$C,$C3+1)),"No Next Data")
G3:H11	G3	=IFERROR(1/(1/SUMIFS(E:E,$A:$A,$A3,$C:$C,$C3-1)),"No Next Data")

 

[surendarmani]

It's not working on this dataset that I am trying to populate these results. This is a large dataset with >13500 rows.

Active_UG_Students_with_GPA_by_Term_v3__with_sorting_edit_in_progress.xlsx
	A	B	C	D	E	F	G	H
1	ID	NAME	ACADEMIC_PERIOD	GPA	PREVIOUS_ACADEMIC_PERIOD	PREVIOUS_TERM_GPA	NEXT_ACADEMIC_PERIOD	NEXT_TERM_GPA
2	101	Student1	201920	2.52941177	No Previous Data			No Next Data
3	101	Student1	202010	2.75	No Previous Data			No Next Data
4	101	Student1	202110	3.4	No Previous Data			No Next Data
5	101	Student1	202120	2.4	No Previous Data			No Next Data
6	102	Student2	202110	4.	No Previous Data			No Next Data
7	102	Student2	202120	4.	No Previous Data			No Next Data
8	102	Student2	202210	4.	No Previous Data			No Next Data
9	103	Student3	201920	3.	No Previous Data			No Next Data
10	103	Student3	202010	3.5	No Previous Data			No Next Data
11	104	Student4	201920	3.25	No Previous Data			No Next Data
12	104	Student4	202010	3.75	No Previous Data			No Next Data
13	104	Student4	202110	4.	No Previous Data			No Next Data
14	104	Student4	202120	3.5	No Previous Data			No Next Data
15	105	Student5	201920	1.75	No Previous Data			No Next Data
Any_semesters_with_valid_GP (2

Cell Formulas
Range	Formula
E2:E15	E2	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$A2,$C:$C,$C2-1)),"No Previous Data")
H2:H15	H2	=IFERROR(1/(1/SUMIFS(F:F,$A:$A,$A2,$C:$C,$C2+1)),"No Next Data")

Named Ranges
Name	Refers To	Cells
'Any_semesters_with_valid_GP (2'!_FilterDatabase	='Any_semesters_with_valid_GP (2'!$A$1:$H$13860	H2:H15, E2:E15

But it worked fine with the previous dataset which had <15 rows.

Is there any specific reason the formula isn't working on this one?

Thanks
Surendar

 

[Fluff]

Is there any specific reason the formula isn't working on this one?

Yes. In your previous data you had years in col C, now you don't, so cannot simply add or subtract one from that value.

 

[surendarmani]

Appreciate you looking into this and helping me to understand the issue. Is there any workaround to get the required next and previous row values for this kinda dataset?

Surendar

 

[Fluff]

Yes it's possible, but as it's now a significantly different question, it needs a new thread.
You will also need to explain what is considered the next & previous period.

 

[surendarmani]

Yes it's possible, but as it's now a significantly different question, it needs a new thread.
You will also need to explain what is considered the next & previous period.

Sure, I will create a new thread then and explain the details there.

Thanks again

 

[Fluff]

My pleasure.