Excel Help!!! eeeek

iamtheoneneo

iamtheoneneo

iamtheoneneo

Soldato
Joined
15 Mar 2010
Posts
11,523
Location
Bucks
I have forgotten how to do something and need to remember can YOU help?

i have a table with columns-

SID | subject1 | grade1 | subject2 | grade2 | subject3| grade3 | subject4 | grade4 etc etc.

0 biology A Maths A etc...
1 Maths B French B etc ...
etc....

and i need to make it

SID / subject / grade
0 Biology A
0 Maths A
1 etc etc

now i remeber being able to take 2/3 columns from the raw data and pressing a shortcut key (like CTRL+SHIFT+ something) to bring up a box and it arranges them in the way i want it but i cant remember what it is!!!!!

its like transposing but not quite....

any help?>>
 
figured it out. It was ALT+D, P

For those that care:

It brings up pivot wizard and you create a consolidated range of a group of columns (ie subject), once the table has been created remove all but the values from the field list and double click on the count of value to bring up the first part of your table. then repeat for the subjects and job done.
 
iamtheoneneo said:
figured it out. It was ALT+D, P

For those that care:

It brings up pivot wizard and you create a consolidated range of a group of columns (ie subject), once the table has been created remove all but the values from the field list and double click on the count of value to bring up the first part of your table. then repeat for the subjects and job done.
Click to expand...

Well I never knew pivot tables could do this. Can you show a screen dump of your pivot table field list? I can't see how you did the bit I've quoted in bold.
 
ok so say you start with this:
1-6.png


id do alt+d, p and select consolidated range and then the table, this will create the pivot like:
2-5.png


remove all fields circled in red and then double click on the total which will then show the table you need.
3-3.png


it works with tables that have different columns at the beginning you just need to put them in their own table first and go through the steps - joining the all up once finished.i then just delete columns b and d and rename those that are left to subject and id (in this example)
 
Thanks, I will look at this in more detail later, but my first reactions are:

- Why do you need to remove the fields? You can just double click the grand total anyway.
- Where are the grades? Does it still work with grades in? :p
- I don't think you need to use a consolidated range for these purposes, that's for multiple worksheets I think?
 
1. you dont have to but i just do it by default to make sure my data is exactly what i want
2. you need to do it for each column type, it wont work otherwise.
3. creating a consolidated range means less work for the user and you are guaranteed that the pivot is correct.
 
You must log in or register to reply here.
Back
Top Bottom