# Rearranging data in Excel

• Hi,
I'm not sure how to ask this question without illustrating it, so here goes,
I have results from a test which has tested peoples attitudes (scores 1-5) to different voices on a 16 different scales. The data set looks like this (where P1,P2,P3 are participants, A, B, C are voices)

Aformal Apleasant Acool Bformal etc
P1 2 3 1 4
P2 5 4 2 4
P3 1 4 4 3

However, I want to rearrange my data to look like this>
formal pleasant cool
P1A 3 3 5
P1B 2 1 6
P1C etc
P1D

This would mean a lot more rows (multiple rows per participant), and a lot fewer columns. Is it doable without having to manually reenter all the scores in a new excel file?

• Re: Rearranging data in Excel

Maybe it's late in the day and my brain has slowed to a crawl, but I'm really not sure I follow the logic of your sample result. I see what you're saying about reorganizing the data, but then the numbers you show for the result don't seem to have any relationship to the initial set of numbers. You might do best to attach a sample with slightly larger and more representative selection of real data.

• Re: Rearranging data in Excel

Sorry about not being clear. I hope this will help: 70 participants have filled out evaluation forms with 11x16 scales. They have listened to 11 voices and they have evaluated each voice on 16 qualities, for instance how formal, how cool, how pleasant this voice is. I have one row for each participant, and then one column for each quality per voice, giving 176 columns. This makes stuff like One-way ANOVAs and PCA difficult, so I would like to have one column for each quality =16 columns, and then one row for each participant's evaluation of each voice, so 70x11=770 rows.

• Re: Rearranging data in Excel

[Wigi: deleted - same post as above]

• Re: Rearranging data in Excel

Since I had already worked out a solution by the time I saw Herbds7's post, I'll go ahead and put it up. This is one of those cases where it's just amazing to see how different the approaches to solution can be and still reach the same goal. Now you get to pick the one that fits your real life conditions best. Since you didn't post sample data, I assumed everything you said about your scenario was 100% literally true. So to make this work, all you have to do is have the sheet with your original data active, then run this code. The results will be on a new sheet, so your original data remains unchanged.

Assumes: 1) no blank lines or columns in your data; 2) header row is in row 1; 3) people labels(P1, P2, P3, etc...) are in column A.

• Re: Rearranging data in Excel

Herbds7: Wow, thank you SO much for this pedagogical tutorial! I followed each step carefully, and everything went quite well, until the very end: My pivottable ended up with only values of 1 in each cell. Apart from that, it looks the same, except for totals added for each participant, and it says "Count of Rank" instead of "Sum of Rank". The rank values were right in Step 3, but only 1s in Step 2 as well. So close!

• Re: Rearranging data in Excel

Barb-B: Thank you for this. It is so far my Plan B, as I flinch from code... But I very much appreciate the work you must have put into this!

• Re: Rearranging data in Excel

Ulrikke,
As much as I appreciate your effort to be appreciative, I think I would have liked to know of your aversion to a coded solution before I went to the effort of working one out. Maybe next time you post a request for help you can include that detail.

• Re: Rearranging data in Excel

Click on "Count of Rank" in the PivotTable and
change the setting to "Sum".
The same thing can be done in the PivotTable Field List
or by right clicking the PT and picking "Summarize Values by" "Sum".
The "1"s in Step 2 are OK.
Here is an automated version with VBA for the greater community:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

• Re: Rearranging data in Excel

Great, thank you so much!

• Re: Rearranging data in Excel

Barb,
Your effort was surely appreciated by many other community members.
I included your fine macro in my latest file:
http://c3017412.r12.cf0.rackcdn.com/04_23_11.xlsm
Surely the browsers should be our main target.

• Re: Rearranging data in Excel

Herbds7, Thank you - that was nice.

And Ulrikke, sorry if I was any snarky about it. As much as anything else, the failure was my own self-discipline - as I ignored my own real work because I found your little problem intriguing.

• Re: Rearranging data in Excel

I deserved it, Barb-B! I'm new to the game and I apologize. Thank you for ignoring your own work for other people's problems - you're rescuing a lot of people from data embarrassment.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!