How to list all fields using Pivot table Function 3

[wec43wec]

I have a large data base that I am using to sum totals of various products, however when using pivot tables(PT) it will show the name and sum of each item (product), but not a secondary or in my case a primary field such as State below.

As an example, I may have the following

State Product Revenue

CA Apple 100
CA Apple 200
CA Apple 100
CA Apple 300
CA Orange 100
CA Orange 100
CA Orange 300


The PT function will "list/display" the Product "name" and the "sum" for Apple and the product "name" and "sum" for orange, however it will only show the state name on "one line" and NOT on each line showing the sum of EACH product by state name.

Note: my data base has over 20,000 rows and as an example multiple state names.

Thanks for any help !

 

[fneily]

Don't know what your layout is but I have State has Row, Product as Column, Revenue as Data. Looks ok. I tried State has column and Product as Row. Still looks ok.

 

[wec43wec]

fineily - thanks for your reply, however my issue ia a little more complicated than my origial post in that I thought I coule use one data field (revenue) and replicate any suggestions from the forum to apply to my PT.

The major issue in this second example which is an extension of the previous post is that I need the value under "State" to show for "each" sum of a product. Because of my large data base, the value for "State" will never be the same in that I would have products to sum from other states. When I use "subtotal" it does not allow me to include the value of state on each row that provides a sum for each product.

I have provided a list of the "Raw" data and the "desire" results/format to give my issue a better understanding to the forum.

State Product Revenue Pieces Weight
CA Apple 100 100 100
CA Apple 100 100 100
CA Apple 100 100 100
CA Apple 100 100 100
CA Orange 200 200 200
CA Orange 200 200 200
CA Orange 200 200 200
CA Peach 300 300 300
CA Peach 300 300 300
CA Peach 300 300 300


Desired
Results
Below



State Product Revenue Pieces Weight
CA Apple 400 400 400
"CA" Orange 600 600 600
"CA" Peach 900 900 900

 

[Gavona]

I don't believe it can be done directly. What I do is copy the pivot table, pastespecial, values

Now it is no longer a pivot table.
Highlight the entire State column of your table.
Edit,Goto, special, blanks
(you now have all the blank cells selected that you want to show the State)
press the = sign then UpArrow Ctrl-Enter
(your blanks should now all contain a formula that picks up the value from the cell above)

You may well want to:
Highlight the entire State column of your table
Copy,
Paste Special, values

Regards


Gavin

 

[Lilliabeth]

I really don't understand the first part of your post, but looking at your desired output, doesn't this work:

Make State be row field
Make Product be row field
Make each of the others be column fields

--Lilliabeth

 

[fneily]

This will look complicated, but it's not. We'll do an MS Query and write our own SQL.
Open you spreadsheet. First, select your list, including titles. In the name box, upper left where the cell reference is, type in a name. I called my StateProducts. You must use name ranges in MS Query.

Click anywhere in the list.
Click Data - Get External Data - New Database Query.
Select New Data Source (should already be selected). Click Ok.

Give it a name in the first box. eg. Tektip1
Select the Microsoft Excel Driver in the second box from the dropdown.
Click Connect.
Click Select Workbook button. Browse to the workbook on the left. Click Ok. Click Ok. Click Ok. You should be back at Choose Data Source.

UNCHECK option at bottom that says Use the Query Wizard to Create....

Click Ok.

Select the tablename. Click the Add button. Click the Close.

At top, Click the SQL button. Type in (or copy/paste) this:
(Note: Select statement is on one line)

Select State, Product, Sum(Revenue), Sum(Pieces), Sum(Weights)
From StateProducts
Group By State, Product;

Click Ok.
Click OK when message appears.

Click File - Return Data to Microsoft Excel. Select a new sheet or some large empty area.

Change Titles over sum columns:

You should have:
State Product Revenue Pieces Weights
CA Apples 400 400 400
CA Oranges 600 600 600
Etc.

 

[Lilliabeth]

Sorry, I just realized that what I told you only works with Excel 2007, not earlier.

--Lilliabeth

 

[Lilliabeth]

I am confused; some one please help me out. I probably missed something really obvious (I do that sometimes!)

How does the desired output differ from this Pivot table?

http://s406.photobucket.com/albums/pp147/lilliabeth/?action=view&current=pivot.gif

--Lilliabeth

 

[fneily]

For results - nothing. That's also what I did first. So I'm assuming he has a hard-headed boss that wants a certain format. Plus it gave me some practice with MS Query.

 

[SkipVought]

1. You could try putting the STATE in a PAGE field.

or

2. You could try using MS Query, using a list of states as a source for a Data > validation field and the Selection Result of that drop down as the criteria value for the Query, as a Parameter query. The change in State value, can execute the query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fneily]

SkipVought - Good idea. I once had a person who printed off an excel sheet which had a dropdown like that. They actually asked me how to operate the dropdown on the paper. Later, I watched them cross the street out of curiosity.

 

[wec43wec]

To all - thanks for the help / response.

I just got back in the house / on my computer to read your responses.


Lilliabeth - the main difference from my desired results versus the PT results is that the State column will not contain the actual state name on “each row” that contains the sum of each product type if the state has more then one produc type / item.

In other words cell A2 would have CA, B2 thru E9 would have the data >> (Product, Revenue(sum), Pieces(sum) and Weight (sum). Now, “column”– A3 thru A5 would be "blank". I am looking for A3 thru A5 to have the name of the state which is in cell A2 or the state name for the actual product types. Remember, this is a very large data base.

Gavona - I tried your suggestion, but this may not work in that the state column will not have the same name for all blank cells. However, you did show me a little trick with the = up arrow and crtl/enter. Does this only work on edit> goto > special> balnks?


Skip - as usually, you always seem to steer me in a direction to learn something different and beneficial to me in the long run. I do not know MS query, but I guess I will start now if this is the only way for me to get this data in the format indicated above as the desired results.

Skip – question, would Access be a better alternative and then copy the results into excel? ! I do not know access very well - just a little, but if this is a better option, I will learn that portion of access, if not I will go to MS query.

Again - thanks to all !

Thanks and a STAR again to you, Gavona and finely.

 

[SkipVought]

would Access be a better alternative and then copy the results into excel?"

Not necessarily, without knowing more about the specifics. Generally, I do most of my queries in Excel, if they need to end up in Excel. It seems that your source data is already in Excel.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fneily]

I will learn that portion of access". Not quite. You just can't copy your spreadsheet into an Access table. They are NOT the same structure. Every company I've taught in has someone doing that and it's wrong. Access tables are normalized while Excel lists (not tables) don't have to be.

Actually, you'll see in my post SQL which gets the job done. MS Query is just an interface to SQL.

 

[wec43wec]

Finely - the query worked EXTREMELY WELL !

Saying thanks or giving you a STAR is not really not enough.

Question to you and Skip - can you give me a few examples of other situations where you would use MS Query versus other “normal” Excel functions. I say this because I am not use to the MS Query function in Excel and it simplified my task immensely.

Please advise - again thanks and take another STAR !

 

[SkipVought]

MS Query is just another tool that one can have in their toolbox. As you use a tool, you begin to discover more uses for it. It takes time and experience.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Gavona - I tried your suggestion, but this may not work in that the state column will not have the same name for all blank cells. However, you did show me a little trick with the = up arrow and crtl/enter. Does this only work on edit> goto > special> balnks?

The solution I posted does work as each blank cell needs to have the state value from the row above and this is what the formula would do. This might help you to understand:

If you highlight (select) a range of cells only one of those cells is active. You can tell which by looking at the Name box on the left of the Formula bar.

If you have a number of cells selected, enter a formula or text and then press Enter. The formula will enter ONLY into the (single) active cell. If however you hold down Ctrl while pressing Enter then the formula (or text) will enter into ALL the selected cells.

Let us assume that the active cell is A6.
If the formula you created uses absolute addressing (e.g. =$A$5 ) then as you suggest all the formulae will refer to cell A5.
However, if you use relative addressing (e.g. =A5 ) then the formula in each selected cell will all refer to the cell in the same relative position - in this example in the same column but one row above each selected cell.

Finally, you can construct the formula in any way you like. That includes using the mouse, typing it in directly or using arrow keys. You asked [red]"Does this only work on..."[/red] Great question. I really would advise that you try it to find out - that is by far the best way of learning.

Gavin

 

[fneily]

When to use MS Query? Good question.
First, MS Query is not a function per se. It's a program, and an add-in at that. See Tools - Addins. You'll see MS Query in the list.

As SkipVought said, time and experience. But to generalize, Excel is used to keep lists and to do some kind of analysis, usually mathematical. With yours, your doing math, summing, but also relating - States with products. That's what Access does extremely well. It is, after all, a Relational Database Management System. And MS Query is like, if not derived from, a query in Access, it works well with normalized tables(in Access) or lists (in Excel). Few people normalize their Excel lists. Actually, I've never met anyone in any company I taught training in that did or knows how.

So programs like Pivot tables, Crosstabs, etc. are good for summarizing which is mathematical - sum, count, min, max, avg, etc. Even grouping. But relating, then you need to join data together and that takes a query. Recognizing that, though, takes a little skill from experience.

Also, remember it if you have to capture data from an Access database.

 

[wec43wec]

finely - thanks once again for your reply and excellent comments relative to MS Query which I have on my computer and I like to believe I have a good understanding of what you've taught me thus far in the above post. You examples - I followed with complete understanding !

I have very limited experience with SQL, however I am not totally understanding your comments of >>>> Few people "normalize" their Excel lists. Actually, I've never met anyone in any company I taught training in that did or knows how. <<<<

Question - what is meant by normalizing their excel file?

Please advise.

Thanks

 

[fneily]

Here's the standard reference we give in the Access forums for normalization and relationships:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You can also look at the book:
Excel Advanced Report Development

In your case, if there is no other data related to State or Product, your list is normalized. However, notice the field Revenue. That's a total created from (Number of Apples) X (cost per apple,or whatever). Where do you keep this initial price? You can't keep it your table, wouldn't make sense. So you need two tables. You'd have another worksheet for products that would look like:
Product PricePerWhatever
apple .60
orange .45
etc.

Now the two tables would be join in a query and the total cost calculated. You would not have the field Revenue. You don't store totals in normalized tables. No need.

Let's do another example. You have customers, sellers and thus orders.
One worksheet would look like:
CustNum Fname Lname Address Etc
1000 Rick Smith
1002 Bob Jones

Another:
SalerID SalesPerson
A1 Jane
A3 Carol

And the last worksheet
OrdNum OrdDate CustNum SalerID Amount
1002 1/12/08 1002 A3 $21.80
etc.

Then you would name each list with their column headings. MS Query only works with named ranges. So tblCustomer, tblSeller and tblOrders.

This is exactly what Access tables would look like. Normalized. Notice how they are connected, through a common field, usually some ID. Now you can analyze through MS Query.
Now to expand, tblCustomer and tblSeller are your main tables. Only info related to that topic is in the table. The tblOrders is called a junction table. It includes, at minimum, the ID's of the two tables you want to join and any COMMON data.

So you can see this takes a little effort to construct. Most people who use Excel never heard of normalization. Most people using Access don't know how to normalized, in my experience and opinion.