MS Excel - How can I summarize a table? Will Pivot Table Reports do this for me?

Discussion in 'Computer Support' started by hugh jass, Sep 10, 2003.

  1. hugh jass

    hugh jass Guest

    Hello,
    I am trying to do a table summary, which I believe I can accomplish with the
    Pivot Table Report.
    Can someone please help with what I am trying to do?

    Using the following table (figure 1) as an example (comma separated incase
    you'd like to copy the text, save as a .csv file, then import into Excel -
    didn't want to post any attachments), I would like to build a summary based
    on specific columns.
    Essentially, I want the following information:
    Number of cars sold by Manufacturer, date and dealer, similar to below.
    manufacturer: Ford, date: 09/09/2003, dealer: 1, 1 cars sold
    manufacturer: Ford, date: 09/09/2003, dealer: 2, 1 cars sold
    manufacturer: Citroen, date: 08/09/2003, dealer: 1, 2 cars sold, etc...
    Can this be done in Excel?? If so, how do you do it? How do you deal with
    the fact that this is not all numeric data?
    THanks in advance, hj.


    figure 1.
    Used Cars Recently Sold in Yourtown, OH.
    manufacturer,model,license,date,dealer
    Ford,Edsel,ewr-234,09/09/2003,1
    Ford,Focus,eru-743,09/09/2003,2
    Toyota,Landcruiser,itr-393,07/09/2003,3
    Toyota,Corona,ack-394,09/09/2003,3
    Toyota,Corona,krm-584,06/09/2003,2
    Citroen,DSpécial ,mfh-854,08/09/2003,2
    Citroen,Pallas,ner-936,08/09/2003,1
    Citroen,Pallas,mke-483,08/09/2003,1
    Honda,Civic,nkr-486,08/09/2003,2
    Honda,Civic,isk-438,07/09/2003,3
    Honda,Accord,wdl-348,07/09/2003,1

    ______________________
    "peace - it's worth killing for"
    - Bush
     
    hugh jass, Sep 10, 2003
    #1
    1. Advertising

  2. hugh jass

    you suck Guest

    firstly highlight all of the data that you want in the report. then click on
    Data/Pivot table report and follow the instructions from there. It may not
    be immediatley apparent but it is not easy to explain without being there in
    person.

    trial and error is good though.


    "hugh jass" <> wrote in message
    news:ilK7b.3238$...
    > Hello,
    > I am trying to do a table summary, which I believe I can accomplish with

    the
    > Pivot Table Report.
    > Can someone please help with what I am trying to do?
    >
    > Using the following table (figure 1) as an example (comma separated incase
    > you'd like to copy the text, save as a .csv file, then import into Excel -
    > didn't want to post any attachments), I would like to build a summary

    based
    > on specific columns.
    > Essentially, I want the following information:
    > Number of cars sold by Manufacturer, date and dealer, similar to below.
    > manufacturer: Ford, date: 09/09/2003, dealer: 1, 1 cars sold
    > manufacturer: Ford, date: 09/09/2003, dealer: 2, 1 cars sold
    > manufacturer: Citroen, date: 08/09/2003, dealer: 1, 2 cars sold, etc...
    > Can this be done in Excel?? If so, how do you do it? How do you deal

    with
    > the fact that this is not all numeric data?
    > THanks in advance, hj.
    >
    >
    > figure 1.
    > Used Cars Recently Sold in Yourtown, OH.
    > manufacturer,model,license,date,dealer
    > Ford,Edsel,ewr-234,09/09/2003,1
    > Ford,Focus,eru-743,09/09/2003,2
    > Toyota,Landcruiser,itr-393,07/09/2003,3
    > Toyota,Corona,ack-394,09/09/2003,3
    > Toyota,Corona,krm-584,06/09/2003,2
    > Citroen,DSpécial ,mfh-854,08/09/2003,2
    > Citroen,Pallas,ner-936,08/09/2003,1
    > Citroen,Pallas,mke-483,08/09/2003,1
    > Honda,Civic,nkr-486,08/09/2003,2
    > Honda,Civic,isk-438,07/09/2003,3
    > Honda,Accord,wdl-348,07/09/2003,1
    >
    > ______________________
    > "peace - it's worth killing for"
    > - Bush
    >
    >
    >
     
    you suck, Sep 10, 2003
    #2
    1. Advertising

  3. hugh jass

    Ken Wright Guest

    Yoir data was made for a Pivot Table. For starters though, assuming yoir data is in csv format,
    change the extension to .txt and open it. This will kick off the text import wizard, where you
    will be able to specify the formatting of individual columns of you so wish. You want to choose
    the Delimited option, ticking comm as delimiter.

    Once this is done you need to select all the data and do Data / Pivot Table and Chart Report.

    For a step by step, take a look at the following links:-

    Debra Dalgleish's pictures at Jon Peltier's site:
    http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
    And Debra's own site:
    http://www.contextures.com/xlPivot01.html

    John Walkenbach also has some at:
    http://j-walk.com/ss/excel/files/general.htm
    (look for Tony Gwynn's Hit Database)

    Chip Pearson keeps Harald Staff's notes at:
    http://www.cpearson.com/excel/pivots.htm

    MS has some at (xl2000 and xl2002):
    http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
    http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL2K & XLXP

    ----------------------------------------------------------------------------
    Attitude - A little thing that makes a BIG difference
    ----------------------------------------------------------------------------



    "hugh jass" <> wrote in message
    news:ilK7b.3238$...
    > Hello,
    > I am trying to do a table summary, which I believe I can accomplish with the
    > Pivot Table Report.
    > Can someone please help with what I am trying to do?
    >
    > Using the following table (figure 1) as an example (comma separated incase
    > you'd like to copy the text, save as a .csv file, then import into Excel -
    > didn't want to post any attachments), I would like to build a summary based
    > on specific columns.
    > Essentially, I want the following information:
    > Number of cars sold by Manufacturer, date and dealer, similar to below.
    > manufacturer: Ford, date: 09/09/2003, dealer: 1, 1 cars sold
    > manufacturer: Ford, date: 09/09/2003, dealer: 2, 1 cars sold
    > manufacturer: Citroen, date: 08/09/2003, dealer: 1, 2 cars sold, etc...
    > Can this be done in Excel?? If so, how do you do it? How do you deal with
    > the fact that this is not all numeric data?
    > THanks in advance, hj.
    >
    >
    > figure 1.
    > Used Cars Recently Sold in Yourtown, OH.
    > manufacturer,model,license,date,dealer
    > Ford,Edsel,ewr-234,09/09/2003,1
    > Ford,Focus,eru-743,09/09/2003,2
    > Toyota,Landcruiser,itr-393,07/09/2003,3
    > Toyota,Corona,ack-394,09/09/2003,3
    > Toyota,Corona,krm-584,06/09/2003,2
    > Citroen,DSpécial ,mfh-854,08/09/2003,2
    > Citroen,Pallas,ner-936,08/09/2003,1
    > Citroen,Pallas,mke-483,08/09/2003,1
    > Honda,Civic,nkr-486,08/09/2003,2
    > Honda,Civic,isk-438,07/09/2003,3
    > Honda,Accord,wdl-348,07/09/2003,1
    >
    > ______________________
    > "peace - it's worth killing for"
    > - Bush
    >
    >
    >
     
    Ken Wright, Sep 10, 2003
    #3
  4. hugh jass

    Helen Guest

    Highlight ALL the date you want to put into Excel. Go to Edit at top and
    press copy. Then open Excel and when the page is open go to Edit and paste.
    THEN
    highlight the TOP row (I've found it works best when you do only one column
    at a time) A[Your Title for the column here] B[col title] etc. Highlight
    A then
    go to Format and select AUTOMATIC FIT. Do this procedure for each column.
    Then go to file SAVE AS.... give it a name... and there you are. If you
    want lines in it
    you will have to check the page set up and go to the tab after
    headers/footers ... I've forgotten right off... but there's a place to check
    if you want graph style line. Put a check in it. Exit SAVE then press
    File /Print Setup to see if it is
    the way you want it. If not, press File/Page Setup and change it to the way
    you want it. HTH

    Helen


    "hugh jass" <> wrote in message
    news:ilK7b.3238$...
    : Hello,
    : I am trying to do a table summary, which I believe I can accomplish with
    the
    : Pivot Table Report.
    : Can someone please help with what I am trying to do?
    :
    : Using the following table (figure 1) as an example (comma separated incase
    : you'd like to copy the text, save as a .csv file, then import into Excel -
    : didn't want to post any attachments), I would like to build a summary
    based
    : on specific columns.
    : Essentially, I want the following information:
    : Number of cars sold by Manufacturer, date and dealer, similar to below.
    : manufacturer: Ford, date: 09/09/2003, dealer: 1, 1 cars sold
    : manufacturer: Ford, date: 09/09/2003, dealer: 2, 1 cars sold
    : manufacturer: Citroen, date: 08/09/2003, dealer: 1, 2 cars sold, etc...
    : Can this be done in Excel?? If so, how do you do it? How do you deal
    with
    : the fact that this is not all numeric data?
    : THanks in advance, hj.
    :
    :
    : figure 1.
    : Used Cars Recently Sold in Yourtown, OH.
    : manufacturer,model,license,date,dealer
    : Ford,Edsel,ewr-234,09/09/2003,1
    : Ford,Focus,eru-743,09/09/2003,2
    : Toyota,Landcruiser,itr-393,07/09/2003,3
    : Toyota,Corona,ack-394,09/09/2003,3
    : Toyota,Corona,krm-584,06/09/2003,2
    : Citroen,DSpécial ,mfh-854,08/09/2003,2
    : Citroen,Pallas,ner-936,08/09/2003,1
    : Citroen,Pallas,mke-483,08/09/2003,1
    : Honda,Civic,nkr-486,08/09/2003,2
    : Honda,Civic,isk-438,07/09/2003,3
    : Honda,Accord,wdl-348,07/09/2003,1
    :
    : ______________________
    : "peace - it's worth killing for"
    : - Bush
    :
    :
    :
     
    Helen, Sep 11, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ws00sw
    Replies:
    10
    Views:
    15,155
    ws00sw
    Mar 3, 2005
  2. yazh

    Pivot Table - Sub totals

    yazh, Sep 22, 2005, in forum: Computer Support
    Replies:
    2
    Views:
    1,196
  3. Bruce Lewis

    summarize picture phones vs compacts?

    Bruce Lewis, Mar 27, 2006, in forum: Digital Photography
    Replies:
    3
    Views:
    421
    Helen
    Mar 27, 2006
  4. KiwiBrian
    Replies:
    4
    Views:
    1,481
  5. Giuen
    Replies:
    0
    Views:
    1,261
    Giuen
    Sep 12, 2008
Loading...

Share This Page