[ad_1]
I have an Excel report with a table that is getting it’s data from a CSV file that gets updated every few months from a python script running a SQL query.
My Excel file has the following data structure:
Student_ID | Student | Class | Grade | Year |
---|---|---|---|---|
d9fs73l41 | Bob M. | PSY103 | B | 2022 |
d9fs73l41 | Bob M. | PHL302 | A | 2021 |
d9fs73l41 | Bob M. | MTH112 | C | 2020 |
d0s83jfos | Jul S. | ARG211 | C | 2021 |
dofs09sje | Ted B. | UNI321 | B | 2022 |
As you can see, it is simply a list of students with their associated ID and course history information containing the course name, the grade they received in that class, and the year they took that class.
OK, now this is what I would like to do with the table: I would like for the Excel file to initially display the data for the latest year for each student, and to keep data for earlier years initially hidden until the user of the Excel file wishes to view that information by clicking on a button next to the row he/she wishes to view, so that the data I mention above would appear like the table below, initially, until the user opts to expand a row on a student to see rows with old data for past years:
Student_ID | Student | Class | Grade | Year |
---|---|---|---|---|
d9fs73l41 | Bob M. | PSY103 | B | 2022 |
d0s83jfos | Jul S. | ARG211 | C | 2021 |
dofs09sje | Ted B. | UNI321 | B | 2022 |
As you can see, only the row pertaining to the latest year for BOB is displayed, since I don’t want the rows for earlier years to be displayed unless the user wishes to expand them.
Basically, I want to do the exact same thing that I was trying to accomplish in SSRS that I describe in this question.. Also on here, someone else had a similar question, but the answer provided allows for a set of rows to be collapsed/hidden underneath a main row for only one set of rows. In the data that I have, I would like to systematically collapse rows for each set of IDs that I have in the table. Is it possible to accomplish this in Excel automatically/programmatically with VBA or through some other means?
[ad_2]