Hello Rick! I hope you are well. First of all thank you so much for taking the time to help out and for sharing your immense knowledge of the workings of Excel. Im not very new to Excel but I am still a beginner. I have been going through your responses and detailed instructions on your website and they have helped bring me closer to finding an efficient solution, but theres still a way to go.
I run a non-profit online education and e-learning research lab. Our research has accelerated due to the COVID-19 epidemic to analyze and evaluate institutional readiness, contingency planning and deployment of online learning platforms.
One of our strengths is our practice tests based on testing skills and their subskills in order to identify strengths, weaknesses, as well as progression over the course of time. Therefore, we have an Excel worksheet that has the results of each test with the student identifier being their email address. Since a lot of students take the same tests, there is a significant number of duplicates when compiling a global result sheet. But there are also those that do not take all the tests. We are working on figuring out a solution to generate a global score report that uses the student email address as their identifier and then picks up their scores from all the result worksheets and gives us their score, test times, etc.
Each test has two parts that are taken separately and so your solution for generating a list of entries from two lists without duplicates worked like a charm. However, I tried to expand the formula to pick up data for more sheets and Excel says that there are too many arguements for that function.
Is there a way that you can recommend where we look across each individual worksheet for the student identifier and then compile these identifiers as one global list without duplicates? I am including a screenshot of one of the reports (green tab) that is fed from two of the raw results sheets in red for the corresponding text. The goal is to generate a global score report, for all students, for all tests (yellow), The next steps we would like to take are to
*create another sheet with a drop down to select a student and generate their individual report card, This report card should have the number data. We would also like to add visualizations to report and ***** learning patters, performance growth, student average compared to class and global data averages.
We are quick learners and are extremely eager to learn more about using this fantastic tool for our purposes and we are keen on learning from you as well as all the other experts here on this forum.
We would be grateful if you could help us get closer to our goal analyzing student data and being able to provide meaningful, constructive feedback to them.
