How to sort data from rows into columns?

[crcurrie]

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?

 

[Max]

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.

 

[crcurrie]

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]

Double-click on the gray field names,
then just select "None" for Subtotals > OK
(default is Auto)

 

[crcurrie]

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

 

[crcurrie]

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

 

[Max]

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)