Forum Discussion
Combining same ID from multiple rows into single row
While I do appreciate your input and recognize the need to see data overtime as an individual row, the current goal for this project is to identify an individual's first enrollment in a post-secondary institution. All of the original data will be retained. After identifying an individual's first enrollment, we are going to be doing things that include frequencies for m/f and race/ethnicity for 2 and 4-year institutions, as well as frequencies of STEM majors by demographic breakdown. The organization we are working with is tracking students after completion of a high school program, and at this moment in time they only want to have information on the very first post-secondary enrollment that students of their program have.
You wrote:
The organization we are working with is tracking students after completion of a high school program, and at this moment in time they only want to have information on the very first post-secondary enrollment that students of their program have.
So here's a way to get, very specifically, at that last. Thank you for a specific goal to achieve.
My formula sifts through your database to get the first enrollment of each student, and does so primarily using the FILTER function, first to get the earliest enrollment date for each student, then to get the rest of that row for each student, showing the institution and major associated with each student's first enrollment date.
The formula looks like this:
=FILTER(Table1[[City ]:[Major]],(Table1[ID]='First Enrollment'!A2)*(Table1[Enroll_Began]=MIN(FILTER(Table1[Enroll_Began],Table1[ID]='First Enrollment'!A2))))
I've attached the sample spreadsheet.