Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data into particular format
We have some results data that has been given to us in an excel spreadsheet
but is not set out as we would like. Not sure how to go about getting how we want it, but I'm sure it must be possible. Data currently has results for each student. The students unique number is listed in column A and the subject studied in B with grades in C, D and E. Each student is listed for each subject they study in rows with grades in the columns for each subject. What we would like is to have the student appearing in the row once and have a column for each subject's grades (ie 3 columns per subject). Data as it is now Column A Column B Column C Column D Column E Student 1 Business A B B Student 1 English A A B What we would like Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng Grd 3 Student1 A B B A A B Any suggestions as to where we start? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data into particular format
Pauls888,
You might look at PivotTables. Select your data, including column headings (make sure you have headings for each column). Make sure there are no blank rows. Go to DataPivotTable and PivotChart Report and just hit finish. Drag "student id" from the little pop-up window to the rows column, "subject" to the header, and grades 1, 2, and 3 to the data. Heidi "Pauls888" wrote: We have some results data that has been given to us in an excel spreadsheet but is not set out as we would like. Not sure how to go about getting how we want it, but I'm sure it must be possible. Data currently has results for each student. The students unique number is listed in column A and the subject studied in B with grades in C, D and E. Each student is listed for each subject they study in rows with grades in the columns for each subject. What we would like is to have the student appearing in the row once and have a column for each subject's grades (ie 3 columns per subject). Data as it is now Column A Column B Column C Column D Column E Student 1 Business A B B Student 1 English A A B What we would like Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng Grd 3 Student1 A B B A A B Any suggestions as to where we start? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data into particular format
Thanks Heidi, but unfortunately that's the first thing we tried - it gives us
the correct layout, but the grades do not appear, only the tally, unless there is a way to show the data in the box rather than the "count" "Heidi" wrote: Pauls888, You might look at PivotTables. Select your data, including column headings (make sure you have headings for each column). Make sure there are no blank rows. Go to DataPivotTable and PivotChart Report and just hit finish. Drag "student id" from the little pop-up window to the rows column, "subject" to the header, and grades 1, 2, and 3 to the data. Heidi "Pauls888" wrote: We have some results data that has been given to us in an excel spreadsheet but is not set out as we would like. Not sure how to go about getting how we want it, but I'm sure it must be possible. Data currently has results for each student. The students unique number is listed in column A and the subject studied in B with grades in C, D and E. Each student is listed for each subject they study in rows with grades in the columns for each subject. What we would like is to have the student appearing in the row once and have a column for each subject's grades (ie 3 columns per subject). Data as it is now Column A Column B Column C Column D Column E Student 1 Business A B B Student 1 English A A B What we would like Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng Grd 3 Student1 A B B A A B Any suggestions as to where we start? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data into particular format
If you right-click in the column, go to "Field Settings" and choose one of
the other options. You're right, it will probably average the grades for you, and not show them individually, though. But, you can at least move beyond "count". Heidi "Pauls888" wrote: Thanks Heidi, but unfortunately that's the first thing we tried - it gives us the correct layout, but the grades do not appear, only the tally, unless there is a way to show the data in the box rather than the "count" "Heidi" wrote: Pauls888, You might look at PivotTables. Select your data, including column headings (make sure you have headings for each column). Make sure there are no blank rows. Go to DataPivotTable and PivotChart Report and just hit finish. Drag "student id" from the little pop-up window to the rows column, "subject" to the header, and grades 1, 2, and 3 to the data. Heidi "Pauls888" wrote: We have some results data that has been given to us in an excel spreadsheet but is not set out as we would like. Not sure how to go about getting how we want it, but I'm sure it must be possible. Data currently has results for each student. The students unique number is listed in column A and the subject studied in B with grades in C, D and E. Each student is listed for each subject they study in rows with grades in the columns for each subject. What we would like is to have the student appearing in the row once and have a column for each subject's grades (ie 3 columns per subject). Data as it is now Column A Column B Column C Column D Column E Student 1 Business A B B Student 1 English A A B What we would like Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng Grd 3 Student1 A B B A A B Any suggestions as to where we start? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data into particular format
Paul,
I just did this with some dummy data and it does exactly what you want. Email me at: hbissell at memphiszoo dot org and I'll send you the file. "Pauls888" wrote: Thanks Heidi, but unfortunately that's the first thing we tried - it gives us the correct layout, but the grades do not appear, only the tally, unless there is a way to show the data in the box rather than the "count" "Heidi" wrote: Pauls888, You might look at PivotTables. Select your data, including column headings (make sure you have headings for each column). Make sure there are no blank rows. Go to DataPivotTable and PivotChart Report and just hit finish. Drag "student id" from the little pop-up window to the rows column, "subject" to the header, and grades 1, 2, and 3 to the data. Heidi "Pauls888" wrote: We have some results data that has been given to us in an excel spreadsheet but is not set out as we would like. Not sure how to go about getting how we want it, but I'm sure it must be possible. Data currently has results for each student. The students unique number is listed in column A and the subject studied in B with grades in C, D and E. Each student is listed for each subject they study in rows with grades in the columns for each subject. What we would like is to have the student appearing in the row once and have a column for each subject's grades (ie 3 columns per subject). Data as it is now Column A Column B Column C Column D Column E Student 1 Business A B B Student 1 English A A B What we would like Column A Bus Grd 1 Bus Grd 2 Bus Grade 3 Eng Grade 1 Eng Grd 2 Eng Grd 3 Student1 A B B A A B Any suggestions as to where we start? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Converting data to list format | Excel Discussion (Misc queries) | |||
Cell data format | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |