csv file from web form - need to mark it against an answer key in excel - help please

JustFiona

New Member
Joined
Jan 13, 2017
Messages
3
Hi,

I've created a web form with 100 questions. Each question has 4 potential answers that are selected by clicking radio boxes. I can export the test answers into a csv file so that each participant is on his own row, and each column has the selected answer to that question.

For example, all on one row... column A has the participant name, B is his answer to question 1, C is his answer to question 2, and so on.

I have the answers to this quiz and would like to put the answers into a spreadsheet and then somehow have a formula/macro to mark the quiz so that I know which answers are wrong.

Is there a tool to do something like this, or can someone point me in the right direction on how to do it in excel? I am not an advanced excel user, so I don't know where to start. Any help would be appreciated.

Thank you!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
I recommend putting the answers on a separate sheet in the same order as they come in from your csv. Then make a 3rd sheet that calculates the results.

Sheet1 has your data
Sheet2 has your answers
Sheet3 has a comparison equation.

The magic happens on Sheet3.

EXAMPLE OF SHEET3
A1 = "=Sheet1!A1"
B1 = "=IF(Sheet1!B1=Sheet2!B$1, 1,0)"


Now copy the equation from B2 all the way to the right, using the drag handle the bottom right of the cell. Drag it all the way to the right until you cover all the questions. You will end up with a bunch of ones and zeros. These ones are correct answers.

NOTE: The dollar sign $ on sheet two means that it will always look at row one as you copy this formula to different cells. This is because your answers will all be on row 1.

After the last question cell do this on the right:

CZ1 = "=SUM(B1:CY1)"

Now CZ column will tell you how many correct answers for that line\student.

Copy this down to cover each student.

Then you could go back to Sheet2 and do summary calculations based on Sheet3 because you only used the first row on this sheet.

Does that make sense at all?
 
Last edited:

JustFiona

New Member
Joined
Jan 13, 2017
Messages
3
Hi! Thanks for this. I think it makes sense to me! Thanks for explaining it so thoroughly. I will certainly give it a try tomorrow and will report back :)

Have a nice evening!

Fiona

I recommend putting the answers on a separate sheet in the same order as they come in from your csv. Then make a 3rd sheet that calculates the results.

Sheet1 has your data
Sheet2 has your answers
Sheet3 has a comparison equation.

The magic happens on Sheet3.

EXAMPLE OF SHEET3
A1 = "=Sheet1!A1"
B1 = "=IF(Sheet1!B1=Sheet2!B$1, 1,0)"


Now copy the equation from B2 all the way to the right, using the drag handle the bottom right of the cell. Drag it all the way to the right until you cover all the questions. You will end up with a bunch of ones and zeros. These ones are correct answers.

NOTE: The dollar sign $ on sheet two means that it will always look at row one as you copy this formula to different cells. This is because your answers will all be on row 1.

After the last question cell do this on the right:

CZ1 = "=SUM(B1:CY1)"

Now CZ column will tell you how many correct answers for that line\student.

Copy this down to cover each student.

Then you could go back to Sheet2 and do summary calculations based on Sheet3 because you only used the first row on this sheet.

Does that make sense at all?
 

JustFiona

New Member
Joined
Jan 13, 2017
Messages
3
Hi Hackslash,

I have followed your instructions and it works like a charm! Problem solved! Thanks very much!!! :)

Fiona

Hi! Thanks for this. I think it makes sense to me! Thanks for explaining it so thoroughly. I will certainly give it a try tomorrow and will report back :)

Have a nice evening!

Fiona
 

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
Fantastic! I'm so happy when imaginary things become real. There is something magical about it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top