Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have duplicate ID values but I need to preserve the data within each row, not just remove it..

Here is what I have currently:

 
ID      course   grade
1       geo        90
2       math       80
3       geo        75
4       math       90
4       sci        90
4       geo        50
5       sci        85

This is what I want:

ID     geo     math     sci     gradegeo     grademath     gradesci
1     1        0        0        90        
2     0        1        0                          80
3     1        0        0        75
4     0        1        1        50                90        90
5     0        0        1                                    85

Hope someone can help:)

ps I think this should be done in a database with multiple tables and a primary key and all that but alas, i do not have access to it.

share|improve this question
1  
I use Excel 2010 and there'sa roundabout way to transpose rows and columns. What I do is copy a selection and paste special a transposed copy. I would think Excel 2007 would have this feature, although probably not located in the same place. –  oscilatingcretin Jan 16 '13 at 17:59
1  
What the OP is asking for is not at all related to transposition, so please disregard the suggestion to use the paste transposition feature of Excel. –  allquixotic Jan 16 '13 at 18:09

2 Answers 2

up vote 1 down vote accepted

Use two pivot tables (for the first: Select your range, Insert, Tables, PivotTable, PivotTable, OK, click in displayed box, drag ID into Row Labels, Course into Column labels and grade into Σ values.)

Copy that pivot table to the right and for the left-hand one ensure Σ values has Count of, for the right hand one Sum of. Hide top row, and the Row Labels column for the right-hand table. Rename column labels as required.

To ensure 0 rather than blank in the left hand table, right click on it, PivotTable Options, Layout & Format, check For empty cells show and insert 0. Format of the grade numbers can be adjusted if necessary by left-clicking on the Σ values content, Value Field Settings (also where to change Sum to Count etc) and Number Format. (If formatting is adjusted directly in the pivot table it will undo at the next refresh).

Overwrite Row Labels with ID and hopefully you will get the result you seek.

Note that if you add data into your source range the pivot tables will not alter until you click on either of them and select Refresh.

share|improve this answer
1  
Better way to do it than my one pivot table because it doesn't require much at all in the way of formatting tweaks once you have the data. My way will work too but they aren't very radically different methods conceptually aside from the formatting. –  allquixotic Jan 16 '13 at 18:11
1  
Great, thanks for your help! Both these methods do the trick! –  hhart Jan 16 '13 at 18:28
    
@allquixotic True. But as I was in the middle of mine before I saw the edit to yours I pressed on regardless. I believe answers can be 'wikified' - no idea of how, but might that be appropriate here? –  pnuts Jan 16 '13 at 18:48
1  
@pnuts a Community Wiki (CW) is only useful in scenarios where there are multiple, completely correct ways to do it that are essentially equal. Since your way is better and cleaner in every conceivable way than mine, a CW would not be suitable, because the two answers are not "equal" in their value -- mine has an added post-processing / formatting step. Yours is indeed the best solution, hence the OP accepted it. Feel free to co-opt my screenshots if you wish. –  allquixotic Jan 16 '13 at 18:58

Use a pivot table. This is not "transposition", per say, but "aggregation" -- terminology difference that should help you in googling :)

  • Enter data

pivot-1

  • Select (highlight) all the relevant data, and create pivot table with Insert -> Pivot Table in "New Worksheet".

pivot-2

  • Customize the pivot table to your needs. In your very specific case, drag and drop the course field to a column label, the ID field to a row label, and drag the grade value to the Values area twice: once, leave it as-is to get the "sum" of the grades (this will only sum up one value unless a single student takes the same course twice in the same semester), and for the other "Sum of grade", left-click on the dropdown arrow and look at the "Value Field Settings". Change it from "Sum" to "Count".

    • Select the entire pivot table, hit Ctrl+C to copy to clipboard, go to a new worksheet, and in the Paste options (dropdown list under the clipboard icon on the Home tab of the Ribbon), do "Paste Values with Source Formatting".

    • Rearrange the columns and tweak the formatting until you get it exactly how you want it. Here's what I did to make it look like your desired output; it took me about 30 seconds after generating the pivot table:

pivot-3

share|improve this answer
    
Thanks for the tip! I will try that. –  hhart Jan 16 '13 at 17:55
1  
Updated with more specific info to your scenario, but learning the general principles of pivot tables will prevent you from ever asking this type of question again, so, since it seems you are a teacher, time to learn :) You like when your students learn the principles rather than just the rote facts, right? Well here's your chance :p –  allquixotic Jan 16 '13 at 18:07
1  
Also, check out the other answer explaining how to eliminate the post-formatting step that I had to do because I didn't think to use two pivot tables side by side. Very clever. –  allquixotic Jan 16 '13 at 18:11
1  
thanks for your help. All I needed was a point in the pivot table direction! I am definitely eager to learn more about the functionalities of pivot tables. Cheers –  hhart Jan 16 '13 at 18:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.