transformation of data?

**schallpattern** · 10-30-2008, 07:44 PM

I got this data (Progress report raw data) from a school software programme with teacher scores for different student behaviours, eg puntuality, organisation, etc, each in a separate worksheet. Stupidly, the software dumped this with all 210 student names in Column A with the particular behaviour for every subject on the curriculum on Row 1.To make matters worse, for each subject, there are 2 teachers, listed eg, punctuality A&D 3D (Art and Design 3D) and punctuality A&D 3D Tb (ie Teacher b).

The format I want the data to be in is in the Report data desired data sheet, where the behaviours are in columns D to H and each student is listed in Column A with Column B denoting the subject (say, 1=A&D 3D, 2=Maths, etc) and the score from Teacher a and Teacher b. This way, I can sort the data SO much more easily in terms of who is has the worst punctuality overall, etc.

I'm an experienced Excel user and I'm having several different attempts at transposing this data but I'd welcome anyone else's input on this. I just need a few pointers.

Thanks in adavance

Schallpattern

**solnajeff** · 10-30-2008, 10:01 PM

Hi

I have attached a sample to see if that is what you are looking for, where duplicates occur in the class it is becasue the originals are a little different.

Please let me know if you would like something different.

Regards

Jeff

**schallpattern** · 10-31-2008, 06:51 AM

THank you so much for looking at this problem. There are two points though,

Your solution is half way there. When I hit the format button you created, it outputs to the rev worksheet with the students and their subjects BUT it needs to bring the data with it with column D, E, F, etc with the data from the other worksheets on the original sheet. So that the output is like the one attached or even dumped into the one attached

Secondly, I don't see how you got to where you did. How did you do that? You know something about Excel that I have never seen. Can you point me in the right direction?

**solnajeff** · 10-31-2008, 12:09 PM

Hi

Here is a revised version that copies data as well.

However you will note that there are some gaps in the data, as far as I can tell it is where columns have been transposed in some of the sheets, i.e. teacher a to b and vice versa, I think it should be okay if this is corrected.

If I get a chance later I will try and explain a bit more.

Regards

Jeff

**schallpattern** · 10-31-2008, 04:51 PM

Well, that's certainly worked though I don't know how you made it do it.

Thank you so much. If you get time, please point me in the right direction of how you did it.

Schall

**solnajeff** · 10-31-2008, 09:34 PM

Hi

As requested I have commented the code, please feel free to ask about anything you are not sure of.

Please Login or Register  to view this content.

Regards

Jeff

**schallpattern** · 11-01-2008, 10:21 AM

Thank you so much again for your help. You've no idea how much time and effort you have saved several people with your help - you've really made a difference to a school and its reporting system. I'm continually amazed by the genorosity of poeple on this board.

Schall

**solnajeff** · 11-02-2008, 10:16 PM

Hi Schall

I have attached a revised version that permits automatic data entry for you to have a look at. I hope that you find it useful in the future.

Please let me know if you have any questions, probably better by message as this thread is now closed.

Regards

Jeff