Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Support > MS Excel - How can I summarize a table? Will Pivot Table Reports do this for me?

Reply
Thread Tools

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

 
 
hugh jass
Guest
Posts: n/a
 
      09-10-2003
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



 
Reply With Quote
 
 
 
 
you suck
Guest
Posts: n/a
 
      09-10-2003
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" <(E-Mail Removed)> wrote in message
news:ilK7b.3238$(E-Mail Removed).. .
> 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
>
>
>



 
Reply With Quote
 
 
 
 
Ken Wright
Guest
Posts: n/a
 
      09-10-2003
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/...ivottables.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/assistan...lconPT101.aspx

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

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



"hugh jass" <(E-Mail Removed)> wrote in message
news:ilK7b.3238$(E-Mail Removed).. .
> 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
>
>
>



 
Reply With Quote
 
Helen
Guest
Posts: n/a
 
      09-10-2003
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" <(E-Mail Removed)> wrote in message
news:ilK7b.3238$(E-Mail Removed).. .
: 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
:
:
:

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
OWC Pivot Table export to excel problem =?Utf-8?B?cmljaGk=?= ASP .Net 0 03-31-2005 09:39 AM
Exporting datagrid to Excel Pivot Table =?Utf-8?B?VGltX2s=?= ASP .Net 0 01-26-2005 06:23 PM
Asp.net reports in the pivot table Brajesh Jain ASP .Net Web Controls 0 09-07-2003 07:56 AM
Asp.net reports in the pivot table Brajesh Jain ASP .Net Datagrid Control 0 09-07-2003 07:56 AM



Advertisments