Extract ordered values for multiple repetitions of an item?

Hi all, does anyone have suggestions for a fast way to do the following:

I have a series of spreadsheets. Each spreadsheet has data from the 10 min. that a student was allowed to repeatedly attempt a series of "9" questions (they could cycle through all "9" questions over and over in random order). I need to track how well they did across questions. So for example, given data:

Q# -- CREDIT
1 -- 20%
5 -- 20%
3 -- 20%
4 -- 20%
9 -- 20%
2 -- 20%
3 -- 20%
6 -- 20%
7 -- 20%
8 -- 20%
2 -- 50%
3 -- 50%
4 -- 50%
5 -- 50%
9 -- 50%
8 -- 50%
7 -- 50%
6 -- 50%
1 -- 50%
3 -- 100%
4 -- 100%
9 -- 100%
1 -- 100%

I need to track their performance across multiple iterations of the same question, such as:

For Q# 1, their performance was 20% on first attempt, 50% on second attempt, 100% on third attempt.

For Q# 2, their performance was 20% on first attempt, 50% on second attempt.

Please note that because there was a time limit and each student went at their own pace, the length of the spreadsheet differs per student and each item had differing numbers of attempts.

Is there an efficient macro that I could run on each spreadsheet (running such a macro individually per spreadsheet is fine) to quickly get that data for all Q#s, and yield organized results (in particular, it would be great to easily know students' second-to-last score)? Note, for simplicity I listed 9 questions, when in fact there are 24. Thanks for any suggestions!

It really depends on how you get your data, the willing/ability to modify the data structure, and how you want the end result to look.

One option would be to run a formula down column C (assuming that Q# is in column A and CREDIT is in column B).

C2 =COUNTIF(A$2:A2,A2)

This would return the attempt #. Then you can build a Pivot Table that shows the question #, attempt #, and score per attempt.
See attachment.

Note that it would be much better to put all of the data in the same worksheet having the following columns; name, Q#, Score, Attempt #.
Then you can go wild with the analysis.

Depending on how your raw data looks, one idea is that you can just do a simple sort by question (data ribbon>Sort). This will group all the Question #s together in numerical order so you can see their progress.

Thank you 63falcondude and dosydos for the excellent suggestions! I'm going to try them out!

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Originally Posted by 63falcondude

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Using pivot tables and your example worked beautifully! Marking as SOLVED.

Great, thanks for coming back to let us know. Happy to help.

Thanks again for the reps!