Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
This is probably a stupid question, but I can't figure out the answer using
the Help resources in Excel 2003. I have a worksheet containing records of individuals and events they have attended. Unfortunately, there is a separate row for each person for each event attended, rather than one row per person with the events attended checked off in columns. For example: NAME EVENT John Smith 12/04/2006 John Smith 10/16/2006 John Smith 06/23/2006 Mary Doe 10/16/2006 Mary Doe 04/03/2006 What I want to get to is this: NAME 12/04/06 10/16/06 06/23/06 04/03/2006 John Smith x x x Mary Doe x x How do I do this in Excel 2003? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
One way is to try a pivot table. Here, it'll get you a good, close-fit result
in a matter of seconds. Click Data Pivot table PivotTable .., then in Layout (Step 3), drag n drop Name in ROW, Event in COLUMN, Event in DATA (it'll appear as COUNT of Event), ok out. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crcurrie" wrote: This is probably a stupid question, but I can't figure out the answer using the Help resources in Excel 2003. I have a worksheet containing records of individuals and events they have attended. Unfortunately, there is a separate row for each person for each event attended, rather than one row per person with the events attended checked off in columns. For example: NAME EVENT John Smith 12/04/2006 John Smith 10/16/2006 John Smith 06/23/2006 Mary Doe 10/16/2006 Mary Doe 04/03/2006 What I want to get to is this: NAME 12/04/06 10/16/06 06/23/06 04/03/2006 John Smith x x x Mary Doe x x How do I do this in Excel 2003? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
Thanks, Max -- I'd always wondered what a "pivot table" was!
One more problem, though: I have a number of other fields besides name for each record (address, phone, etc.). When I create the pivot table, it does a subtotal for each additional column, each calculation constituting an additional row. So I end up with this really long, ungainly worksheet. ANy way to turn off the subtotaling and just have the one row per record? Chris "Max" wrote: One way is to try a pivot table. Here, it'll get you a good, close-fit result in a matter of seconds. Click Data Pivot table PivotTable .., then in Layout (Step 3), drag n drop Name in ROW, Event in COLUMN, Event in DATA (it'll appear as COUNT of Event), ok out. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crcurrie" wrote: This is probably a stupid question, but I can't figure out the answer using the Help resources in Excel 2003. I have a worksheet containing records of individuals and events they have attended. Unfortunately, there is a separate row for each person for each event attended, rather than one row per person with the events attended checked off in columns. For example: NAME EVENT John Smith 12/04/2006 John Smith 10/16/2006 John Smith 06/23/2006 Mary Doe 10/16/2006 Mary Doe 04/03/2006 What I want to get to is this: NAME 12/04/06 10/16/06 06/23/06 04/03/2006 John Smith x x x Mary Doe x x How do I do this in Excel 2003? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
Double-click on the gray field names,
then just select "None" for Subtotals OK (default is Auto) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crcurrie" wrote: Thanks, Max -- I'd always wondered what a "pivot table" was! One more problem, though: I have a number of other fields besides name for each record (address, phone, etc.). When I create the pivot table, it does a subtotal for each additional column, each calculation constituting an additional row. So I end up with this really long, ungainly worksheet. ANy way to turn off the subtotaling and just have the one row per record? Chris |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
Thanks, again -- Max! Now I'm really getting there. A few last qq.:
1. How can I change the positions of the data columns? 2. Is there any way to add more columns to the pivot table, after I've already formatted it? (I can't seem to get back to that window that allows me to add rows and columns.) 3. Is there any way to eliminate the "(blank)" values in the null data fields, so that those cells just show as empty? Chris "Max" wrote: Double-click on the gray field names, then just select "None" for Subtotals OK (default is Auto) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crcurrie" wrote: Thanks, Max -- I'd always wondered what a "pivot table" was! One more problem, though: I have a number of other fields besides name for each record (address, phone, etc.). When I create the pivot table, it does a subtotal for each additional column, each calculation constituting an additional row. So I end up with this really long, ungainly worksheet. ANy way to turn off the subtotaling and just have the one row per record? Chris |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
Well, I figured out the answers to #1 and #2. #3 is a minor issue.
But I'm just realizing that I can't sort the records in the pivot table. And even when I sort the records in the main worksheet and then refresh the pivot table, the pivot table doesn't sort like the main sheet. Is there a way to sort data without having to create the pivot table all over again (I've spent two hours formatting it)? Thanks -- Chris "crcurrie" wrote: Thanks, again -- Max! Now I'm really getting there. A few last qq.: 1. How can I change the positions of the data columns? 2. Is there any way to add more columns to the pivot table, after I've already formatted it? (I can't seem to get back to that window that allows me to add rows and columns.) 3. Is there any way to eliminate the "(blank)" values in the null data fields, so that those cells just show as empty? Chris "Max" wrote: Double-click on the gray field names, then just select "None" for Subtotals OK (default is Auto) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crcurrie" wrote: Thanks, Max -- I'd always wondered what a "pivot table" was! One more problem, though: I have a number of other fields besides name for each record (address, phone, etc.). When I create the pivot table, it does a subtotal for each additional column, each calculation constituting an additional row. So I end up with this really long, ungainly worksheet. ANy way to turn off the subtotaling and just have the one row per record? Chris |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data from rows into columns?
Chris, sorry I'm out here. You could try tinkering with the options
under "Advanced" in the PT Field dialog (dialog when you double click on the gray field names). Maybe hang around awhile for better insights from responders such as Debra Dalgleish to plough in here. But since the posting volume in this newsgroup is very high, I'd suggest you put in a new posting to better surface your new query to Debra's radar. Try browsing also Debra's site at: http://www.contextures.com/tiptech.html (Look at "P" listing - lots of good stuff on pivot table) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 8, 2:06 am, crcurrie wrote: Well, I figured out the answers to #1 and #2. #3 is a minor issue. But I'm just realizing that I can't sort the records in the pivot table. And even when I sort the records in the main worksheet and then refresh the pivot table, the pivot table doesn't sort like the main sheet. Is there a way to sort data without having to create the pivot table all over again (I've spent two hours formatting it)? Thanks -- Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SORT REPETITIVE DATA FROM ROWS TO COLUMNS | Excel Worksheet Functions | |||
Sort on rows (not columns) | Excel Discussion (Misc queries) | |||
How do I sort numbers in a table (ie. rows & columns)? | Excel Discussion (Misc queries) | |||
Sort multiple rows by data in certain columns | Excel Discussion (Misc queries) | |||
data sort is not including all columns in sort | Excel Discussion (Misc queries) |