Forum Discussion
Long data copied to wide data
Hi,
I am trying to do exam analysis and need to copy data from Excel (see image) into my home-made analysis sheet on Numbers mac (see image). The trouble is the exam board data is in a long format data sheet while my numbers table is in a wide format. I have tried copy/paste special/transposing but all that does is run all the candidate names horizontally along with the data horizontally (see image).
What I am looking to try and do is have the candidate names go down in a column and have the data from each question run horizontally across from their name. How can I do that?
Any help would be immensely appreciated - I have been at this for 2 hours now and am sure I could have just single-copied all the data by this point!!!
1 Reply
I assume you open the XLSX file in Numbers and that you do not have Excel on your Mac. I haven't used Numbers for years so I don't really know it all that well. But if you can get the data in the form demonstrated in the picture below "Table1" with candidate names on all rows and remove rows with only the names, you can create a pivot table from it (Rows: candidate, Columns: question, Values: sum of Raw). and make sure the raw and max values are numbers not texts.
This will do the transposition for you. Now I think you can't do much more with that pivot table in Numbers, other than to copy it and paste it as a regular table. Then you can insert your sub-total columns, percentages and the extra header row for the Max scores for each question.
If you have Excel on your Mac, I would skip Numbers altogether. But that's just my personal opinion.
