Grouping data

[Debatewise]

I've created a stop smoking iPhone app for my Psychology MSc. Users are given a mission each day for 21 days, the aim of which to help them with their quit.

Users have the ability to rate missions out of 10, which over 1,000 of them have kindly done. The problem is getting the data into a form where it can be analysed easily.

I've used this formula to transpose rows into columns, with each author having a single row. However, if someone has only entered a rating for day 16, that rating will appear in columns B and C (B being the day, C being the rating for that day). If someone has entered a rating for days 3 and 15 their data will go into the B to E column.

What I'd like is for all ratings for day 1 to be in column C, all for day 2 to be in column E, all for day 3 in column G and so on.

The attached sample data has a number of worksheets.

• Ideal Output: contains the data as I'd like it to be - I achieved this with C&P, which is both time consuming and error-prone.
• Raw Data: Is the data with nothing done to it (apart from some anonymisation)
• MD & MR: Is the data I'd like transformed.

One of the complicating factors is that some users have entered more than one rating for the same day. In an ideal world I'll average this rating (so if someone left a score of 1, 3 and 5 for day 1 I'd average that to 3). However, I understand this might need to be done at a later stage, and perhaps manually.

BTW, this will be an ongoing study, hence the need to work out how I can do this again.

Thank you.

[peterrc]

Hi Debatewise,

I have a query with your Raw Data - see 2 examples below:-

Author Day Rating
0OUICLCchn 2 5
0OUICLCchn 2 10
0OUICLCchn 3 5
0OUICLCchn 3 10
Two different ratings for the same day

0RkJmGtOBn 1 4
0RkJmGtOBn 2 7
0RkJmGtOBn 3 8
0RkJmGtOBn 4 7
0RkJmGtOBn 5 8
0RkJmGtOBn 6 9
0RkJmGtOBn 7 9
0RkJmGtOBn 8 5
0RkJmGtOBn 9 6
0RkJmGtOBn 10 3
0RkJmGtOBn 11 7
0RkJmGtOBn 13 4
0RkJmGtOBn 14 7
0RkJmGtOBn 15 6
0RkJmGtOBn 16 8
0RkJmGtOBn 18 5
Day 12 (and rating) is missing (and has been "transposed" into MD & MR - column D12 is showing 13).

Is the information shown correct?

Regards

peterrc

[Debatewise]

Hi Peterrc,

thanks for the quick reply.

That information is correct. Some users have entered more than one rating for the same day. So with the example above I'd like to average the scores giving 0OUICLCchn a rating of 7.5 for day 2 and 3.

The second problem is the big one though. I'd like an automated way of moving 0RkJmGtOBn's rating for Day 13 and beyond to the relevant column, not under Day 12 where it currently lies. This problem goes throughout the workbook, more than 410 entries are wrongly listed in column 1, 109 of them should be in column 2, 58 in column 3 and so on.

[oeldere]

With an pivot table?

See the attached file.

[Debatewise]

That looks great oeldere, would you be able to explain how you created the pivot table? I'm unfamiliar with using them.

[oeldere]

Excel 2007

on sheet raw data choose

insert => table (you make an table of your data).

After that Insert => pivot table.

Then you get drop boxes on the right side of the file.

You can drag and drop the values in a box.

Just play with it and see the pivot table change.

I used avarage in the value box.

You can also sum them.

You would like to know who answered multiple times.

For that question you need the values to count (you will see in the pivot table the values with e.g. 2 => means 2 answers).

Do you have other questions or comments, just ask or comment.

[Debatewise]

That's great, thank you. I'll have a play with it later and will get back to you if I've got any questions.

[Debatewise]

Got it working just fine, thank you oeldere

[oeldere]

Thanks for the reply.

Glad I could help.

You can add rep(utationpoints) to the members who helped you, by clicking on the star.

[Debatewise]

Reputation already added