Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table

[excelnewbie999]

Hello everyone,

As part of the Olympics celebrations, we've created a couple of trivia quizzes using MS Forms for colleagues. Each MS Forms quiz outputs the results into an excel spreadsheet. From the outputted spreadsheet, the main info I need are:

• Name
• The points
• The amount of time each person took in minutes/seconds
• The ranking/sorting from best to worst for each quiz (in score points & quickest time)
• The ranking/sorting from best to worst from both quizzes combined (in score points & quickest time)
• Then tally this up against both the quizzes done to see who is the winner.

QUIZ 1

Start time	Completion time	Name	Total points
01/08/2024 09:12​	01/08/2024 09:14​	Ruth Wise	3​
01/08/2024 09:12​	01/08/2024 09:14​	Omar Stephens	7​
01/08/2024 09:14​	01/08/2024 09:15​	Erin Howard	7​
01/08/2024 09:15​	01/08/2024 09:18​	Nicholas Ball	5​
01/08/2024 09:34​	01/08/2024 09:35​	Mari Donovan	5​
01/08/2024 09:39​	01/08/2024 09:40​	Harmony Curry	5​
01/08/2024 10:22​	01/08/2024 10:23​	Hasan Gray	4​
01/08/2024 10:22​	01/08/2024 10:24​	Lola Blaese	3​
01/08/2024 10:28​	01/08/2024 10:30​	Zaki Fernandez	4​
01/08/2024 10:33​	01/08/2024 10:35​	Miya Fuentes	4​

QUIZ 2

Start time	Completion time	Name	Total points
31/07/2024 09:15​	31/07/2024 09:16​	Ruth Wise	7​
31/07/2024 09:16​	31/07/2024 09:17​	Omar Stephens	6​
31/07/2024 09:16​	31/07/2024 09:19​	Erin Howard	6​
31/07/2024 09:26​	31/07/2024 09:27​	Nicholas Ball	3​
31/07/2024 09:28​	31/07/2024 09:30​	Mari Donovan	6​
31/07/2024 09:56​	31/07/2024 09:58​	Harmony Curry	0​
31/07/2024 10:00​	31/07/2024 10:03​	Hasan Gray	2​
31/07/2024 10:02​	31/07/2024 10:03​	Lola Blaese	5​
31/07/2024 10:01​	31/07/2024 10:03​	Zaki Fernandez	4​
31/07/2024 10:07​	31/07/2024 10:08​	Miya Fuentes	5​

Any help would be greatly appreciated. Thank you.

 

[Peter_SSs]

Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Pulling data from multiple MS Forms spreadsheets & displaying into single league table
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

Also, this is not the first time you've been told about this, so please make sure it is the last.

 

[excelnewbie999]

Hello @Peter_SSs and @RoryA

Sorry about that. I've not been on this forum for a while, so I forgot. I've tried to edit the original post, but there's no edit option.

Here's the link to the other forum thread: Excel Help

 

[excelnewbie999]

I'm sorry, I've not had any reply, so I'm having to bump this thread.

 

[Peter_SSs]

What actual results are you expecting from the sample data you have provided & how would that be presented?
Best way to show sample data/results is XL2BB

(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[excelnewbie999]

Thank you for your help @Peter_SSs

I'm really sorry, but I'm having trouble with setting up XL2BB. For some reason when I try to install it, it's crashing my Excel 2021.

Basically I want to create table where the individuals total score from both quizzes is added and then ranked. If there is a tie in score, then each individual is scored by how quick they finish their quiz.

I hope that makes sense, but if you have any other questions, please let me know. Thanks.