Forum Discussion
Convert Matrix of Attendance to unique rows
I have a problem that I regularly run into. We get a lot of attendance files that we need to prep for upload to a database. The files have a row for each student and the column headers are dates. Under each date the student is listed as "Absent" or "Present". My end result needs to be a row for each date a member was present.
Program | Name | 02/14/2022 | 02/15/2022 | 2/22/2022 | 2/24/2022 | 02/23/2022 | 03/09/2022 | 03/18/2022 |
Program Name | Student One | Absent | Absent | Present | Present | Present | Present | Absent |
Program Name | Student Two | Absent | Absent | Present | Present | Present | Present | Absent |
Program Name | Student Three | Absent | Absent | Present | Present | Absent | Absent | Absent |
Example of what I need:
Program Name | One, Student | 2/22/2022 |
Program Name | One, Student | 2/24/2022 |
Program Name | One, Student | 2/23/2022 |
I appreciate any suggestions for fast ways to accomplish this. We get a lot of these files with a lot more dates and students on them. Thank you!!
You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The layout of the tables in the screenshot is for illustration. You can place the green table to the right of the blue table or on another worksheet.
- Wow, you are awesome, exactly what I am looking for!
