Excel acrobatics
Author
Discussion

Brown and Boris

Original Poster:

11,838 posts

266 months

Friday 25th July 2008
quotequote all
Not sure iof this is best in P&P or Computers but here goes;

Anybody know a load about Excel?

I have just had a data file sent to me with 3800 scores in (lets say they are exam scores; pretend I work for ETS).

What I need is the individual scores which are curently in single rows (with 4 'sub scores per test), into a single row with all the test scores for that person. There are about 70 tests for each person. The confounding bit is that those 70 tests are from a list of 80 tests so it isn't just a case of sorting by the test name and then transposing the data; everyone has some gaps.

As this is a one-off I can just get some poor student to do it manually but I think that is a week's or more's work by hand to sort 3800 x 4 scores and manualy re-input them in exactly the right place and I need it sooner if at all possible.

I think my software guy could write me a routine (if he wasn't in Antiga) but even that will take a few weeks to get sorted aroound his other work.

ALawson

8,056 posts

282 months

Friday 25th July 2008
quotequote all
Can you use a pivot table?

Also could you give us two line of example data?

Fetchez la vache

5,913 posts

245 months

Friday 25th July 2008
quotequote all
I thought this was about the acrobats at the motor show biggrin

Vee

3,109 posts

265 months

Friday 25th July 2008
quotequote all
Try as I might, I cannot work out what you want to do from your description.
Can you post an extract of the data ?

merc_man

1,926 posts

233 months

Friday 25th July 2008
quotequote all
Just let me know your requirements of how you want the data analyzed, what metrics you want etc. and I can do one off excercise for you.

If you want something in excel to do it on a permanent basis it might take a little longer.

I'm in a meeting for the next 3 hours so let me know if you want anything done and I can get back to you at lunchtime.

Edited by merc_man on Friday 25th July 09:26

Carsie

944 posts

235 months

Friday 25th July 2008
quotequote all
merc_man said:
Just let me know your requirements of how you want the data analyzed, what metrics you want etc. and I can do one off excercise for you.

If you want something in excel to do it on a permanent basis it might take a little longer.

I'm in a meeting for the next 3 hours so let me know if you want anything done and I can get back to you at lunchtime.

Edited by merc_man on Friday 25th July 09:26
You know this is exactly why I really like Pistonheads! Really generous- Well done merc_man clap

merc_man

1,926 posts

233 months

Friday 25th July 2008
quotequote all
Mail sent.

tigger1

8,485 posts

252 months

Friday 25th July 2008
quotequote all
Brown and Boris said:
Not sure iof this is best in P&P or Computers but here goes;

Anybody know a load about Excel?

I have just had a data file sent to me with 3800 scores in (lets say they are exam scores; pretend I work for ETS).

What I need is the individual scores which are curently in single rows (with 4 'sub scores per test), into a single row with all the test scores for that person. There are about 70 tests for each person. The confounding bit is that those 70 tests are from a list of 80 tests so it isn't just a case of sorting by the test name and then transposing the data; everyone has some gaps.

As this is a one-off I can just get some poor student to do it manually but I think that is a week's or more's work by hand to sort 3800 x 4 scores and manualy re-input them in exactly the right place and I need it sooner if at all possible.

I think my software guy could write me a routine (if he wasn't in Antiga) but even that will take a few weeks to get sorted aroound his other work.
If merc_man doesn't / can't deliver - feel free to drop me an email via my profile, this doesn't sound like a big job TBH

Brown and Boris

Original Poster:

11,838 posts

266 months

Friday 25th July 2008
quotequote all
My web guy seems to think he can sort it by running a query on the database. Thanks for the very generous offers. If he can't sort it I may well be back!