Forum Discussion
Combining same ID from multiple rows into single row
As a person who was at one time (I'm retired now nearly two decades) the director of the HR/Payroll database of a major corporation, I have to ask you to step back and consider whether you really want to do this. What you currently have could be described as an accurate history of these students' academic life, including the dates when changes were made. History is better kept as a single row per transaction or per status change. You'd be potentially messing up that history by getting everything for a given student into a single row.
So unless there's a compelling reason for doing that (and as you might guess, I'm skeptical about any such reason), I think you'd be better served leaving the history as it is, looking for ways to create a dashboard where you can see the history of any given individual, school changes, major changes, degrees accomplished, etc.
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.