Mr Excel & excelisfun Trick 20: Summarize Survey Pivot Table

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Hello Everyone

I am trying to find the data source for a pivot table. And when I click on "change data source" tab, it directs me to a single cell. And I can make new pivot table by using that single cell ("a1") as data source.
Can anyone explain to me how this works?

PS: there is no Macro inside, no hidden worksheets

Thanks a million

Does anyone know how to twist the pivot table view from XML/Cascading style to just a normal listing style like Access for Exel 2007? Example:

XML/Cascading style:

COLUMN A & COLUMN B

Product Phone
----------- TV
----------- DVD

Access/Normal View:

Product Phone
Product TV
Product DVD

I played with Pivot & field option but no luck. Hope someone can point me to the right direction. Thanks much!

Sorry if this is noob question - I am trying to complie a skills survey and cant seem to get the pivot table / charts to work the way I need them too.

The data is organized as follows:

name role skill1 skill2 aa eng 1 2 bb sales 2 2 cc sales 3 1 dd eng 2 3 ee eng 1 1 ff eng 1 2


Each person responding to the survey has to list their skill level (1-3) for each skill (a total of 33 skills are in the survey).

Trying to summarize the data with a large Bar Chart in either of two ways:

1-"for each skill - how many people have a skill level of 1,2 and 3" (so skill is X-axis".

2-"for each skill level (1-3) how many people for each skill fall into each skill level category. Skill Level would be X-axis

I also want to be able to filter on "role" so the graphs can reflect the skill distribution based on role (and potentially other critera - ie area, mgr, etc)

Thanks for any assistance you can provide.

Customers 1,2,3,4,5 are taking a survey. The questions are A,B,C,D,E. Their answers will be one of a few set responses: Y,N,Z.

I want to summarize the responses by value, but I'm having trouble structuring the pivot table. Can anyone see what I'm missing?

Given this data:
_ A B C D E
1 Y Y N Y Y
2 Y Y N N N
3 Y Y Y N N
4 Y N N N N
5 Y N N Y Z

I want a pivot table that counts the number of Y, N, and Z responses like this:
_ Y N Z
A 5 0 0
B 3 2 0
C 1 4 0
D 2 3 0
E 1 3 1

Hi everyone.


I have a problem that at first seemed simple, but I can't find the
solution. I'm using a Pivot Table to provide a report of my household
inventory, having columns of quantity, type (each, pair, set) and
description.


What I want to do is provide a report to my moving company which lists
each item in a row, example, 1 each bed, 1 each cupboard, 1 each table.
However, the pivot table summarizes the data and I only get one "1" and
one "each", but individual rows for the description, as you would
expect. What I actually need is a "1" and an "each" on every row for
the moving company report.


I've tried looking at field settings but that doesn't seem to help.
Therefore, is there any way of not summarizing data that is the same,
and treating each row as unique?


Thanks in advance.

I have the following data
Date Task Time
09/22/2008 Task A 1.00
09/23/2008 Task B 1.00
09/24/2008 Task C 1.00
09/25/2008 Task D 1.00
10/01/2008 Task B 2.50
10/07/2008 Task C 0.50
10/13/2008 Task B 5.00
09/29/2008 Task C 1.00
10/02/2008 Task D 0.50
10/06/2008 Task B 2.50
10/09/2008 Task C 0.50
10/14/2008 Task D 1.00
09/23/2008 Task A 1.00
09/24/2008 Task B 1.00
09/25/2008 Task C 2.00
09/26/2008 Task D 2.00
09/30/2008 Task A 1.50
10/02/2008 Task B 3.50
10/07/2008 Task C 1.50
10/08/2008 Task D 2.00
10/09/2008 Task A 1.00
10/03/2008 Task B 5.00
10/08/2008 Task C 2.50
10/09/2008 Task D 2.50

That I want to summarize by day with weekly subtotals like this.

Task Mon Tues Wed Thur Fri
Task A 1 1
Task B 1 1
Task C 1 2
Task D 1 2

week total 1 2 2 3 2

Task A 1.5
Task B 2.5 3.5 5
Task C 1
Task D 0.5

week total 1 1.5 2.5 4.0 5
(sorry, the spacing for the columns won't hold, but it is supposed to shows numbers under the appropriate day.
.
.
.

You get the idea.

I did my research. I figured out how to use weeknum to break it down into weeks and also how to put the days across the top.

My problem now is that the report can span 2 different years so week 53 and week 1 will both be partial weeks and I want to show them as one week.

Is there another way to tackle this?

Also it would be nice where it says week total to say week ending mm/dd/yy (where the date is the Sunday of that week)

Thanks

Is there an easy way to get a Pivot Table to display each record as a % of the Subtotal? I see % of total and % of column, but I would like to see % of the subtotal - each column as a % of a subtotal not the Grand Total. Is this doable? Thanks!

Hi everyone.


I have a problem that at first seemed simple, but I can't find the
solution. I'm using a Pivot Table to provide a report of my household
inventory, having columns of quantity, type (each, pair, set) and
description.


What I want to do is provide a report to my moving company which lists
each item in a row, example, 1 each bed, 1 each cupboard, 1 each table.

However, the pivot table summarizes the data and I only get one "1" and

one "each", but individual rows for the description, as you would
expect. What I actually need is a "1" and an "each" on every row for
the moving company report.


I've tried looking at field settings but that doesn't seem to help.
Therefore, is there any way of not summarizing data that is the same,
and treating each row as unique?


Thanks in advance.