I have a table with two columns. In the first column are names of people. In the second column are scores on a quiz. There are four potential quizzes, and the database that creates this report exports data with a separate name for each quiz. For example, if John Smith completed quizzes 1, 2, and 3, the data would list John Smith three times in the first column with the corresponding quizzes listed, without quiz labels, in the next column over. If Susan Jones completed only quiz 1, she would be listed once. And so on for up to 100+ people. It looks like this:
Column A Column B
John Smith 97
John Smith 75
John Smith 50
Susan Jones 100
I need to be able to transpose this into a table that lists each person once down the table's first column and each quiz result separately in the same row but individual columns, like this (using underscores to try to correct the formatting in this post):
Column J K____L__M_N
John Smith 97 75 50
Susan Jones 100
I tried using OFFSET with ADDRESS to do so, but no luck. My formula was: =OFFSET(ADDRESS(MATCH(J2,A:A,0),1),1,1,1,1)
J2 is the cell where John Smith's name would be in the transposed table. It returned an error (I don't know why), but I also realized that a problem with this approach is I need to account for the fact that John did only 3 quizzes. If I just use the above formula in K, L, M, and N with different offsets, the formula would pull a fourth quiz result for John Smith that he didn't do (it would grab the results for whoever was listed after him in the A:A list).
Advice on how to do this? I don't yet know how to do VBA, and whatever I do, I want to make sure I understand how it works. Currently, I'm doing this manually by copying the cells and using Transpose in paste, but that's tedious and error-prone for large numbers of quizzes/students.
Thank you.
Bookmarks