Creating a new Excel table combining multiple rows of data into one row with multiple columns

Question

Hello,

I have this test score data that I need to manipulate in Excel. Each student ID is listed multiple times for each section of the tests with the scores:

ID	Test Category	Test Score
123	Test 1 Sec 1	4
123	Test 1 Sec 2	4
123	Test 1 Sec 3	3
123	Test 2 Sec 1	2
123	Test 2 Sec 2	4
123	Test 3 Sec 1	4
321	Test 1 Sec 1	2
321	Test 1 Sec 2	3
321	Test 1 Sec 3	2
321	Test 2 Sec 1	4
321	Test 2 Sec 2	1
321	Test 3 Sec 1	2

I need to take that data and create one row per student with each section score in a new column:

ID	Test 1 Sec 1	Test 1 Sec 2	Test 1 Sec 3	Test 2 Sec 1	Test 2 Sec 2	Test 3 Sec 1
123	4	4	3	2	4	4
321	2	3	2	4	1	2

I haven't figured out a way to do this yet in Excel. I'm not sure if this type of problem has been answered before. I have tried to search this using every word combination I can think of, and everything I've found isn't quite right. I have 150 different students, and each have 15 rows for the different Test Categories. It would take a long time to manipulate all this data by hand. Any help would be appreciated.

Thank you for your time and consideration!

Hemmie

Answer 1

That's a layout of a standard Pivot table.

https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

ID into Rows section, Category into Columns section, Score into Values section, done.

Andreas.