Excel sheets with scores by same ID of person (Kahoot) - How to extract and summarize scores from several quizzes?

Question

I've used Kahoot in the classroom and have several excel files with scores from quizzes. Students attended quizzes by using unique IDs. In each file, scores are visible for each ID (but ordered by success on each quiz). There are also some students missing or stating wrong IDs (I'll ignore it).

Now I would like to accumulate all scores for all student IDs in one sheet and summarize them by Student ID.

How can I do that most efficiently? Any pointer or advice is appreciated.

Thanks, B.

question from:https://stackoverflow.com/questions/65906703/excel-sheets-with-scores-by-same-id-of-person-kahoot-how-to-extract-and-summ

Answer 1

Here's a high level guide to getting what you want along with a sample in this file.

Step 1 - Combine Files to Sheet with Unified Columns

Objective

The goal here is to:

• Combine all of your data from other files to single sheet
• Merge the data to be in a single column for each field (i.e. Column A has ID, Column B has score).
• No breaks in rows.
• No formulas.

To illustrate, I made this fake list based loosely on your description.

Method

You probably can do this manually, but a macro could also be used. If you expect to do this year over year, you might look into vba to open close files in a folder. However, since that wasn't part of question, you can do copy-paste (better yet make a kid do it!). Just make sure there's only one header for each column, and all of the data records align. Probably should do copy paste value if you have any formulas.

Step 2 - Show Summation

There's a couple ways this could be done. A pivot table is probably the most sensible because you could include each quiz as a column to see the total. You could also use a pivot table to do averages by student etc.

TO make a pivot table, I would recommend going on YouTube and they will do a better job of explaining than me.

On that same file I made as an example, I included some tabs to illustrate the power of pivot tables and a couple graphs.

Hope that helps. If you have specific technical questions on this, you might consider asking separately.