Make a pivot table from an existing pivot table

I'm trying to make a completely new pivot table out of an existing
pivot table where the existing table is actually the source data (not
the original data table).  The problem I have is that this intermediate
pivot table has 3 columns of unique values (with over 1000 rows) before
we get to the data.  I can't just copy it and make a pivot table, or
create a mirror sheet that isn't a pivot table because of the blank
cells underneath each change in value.

I have this:

Source   Name   Attempts  Data
Mail       Joe       1             654
                         2             700
             Bob      1             300
                         2             400
                         3             800
Phone   Joe        1             324
            Bob        1             466

And need this to pivot:
Mail   Joe   1  654
Mail   Joe   2  700
Mail   Bob  1  300
Mail   Bob  2  400
Mail   Bob  3  800
Phone Joe  1  324
Phone Bob  1  466

Do I have any options other than manually entering the repeating data
in blank cells?  What do I do if I want it dynamic and have the 2nd
pivot table change when the original data is updated?

Thanks!

0
canoako (2)
9/19/2006 9:27:51 PM
excel 39879 articles. 2 followers. Follow

2 Replies
646 Views

Similar Articles

[PageSpeed] 9

You can select those cells and do 
Edit|Copy followed by Edit|Paste special|values.

Then you don't have a pivottable.  (You may want to paste to a new worksheet to
retain the existing pivottable.)

But after you do that, you can fill those gaps by following the techniques at
Debra Dalgleish's site:
http://contextures.com/xlDataEntry02.html

I find the manual technique quicker than the program approach.

CanoAko wrote:
> 
> I'm trying to make a completely new pivot table out of an existing
> pivot table where the existing table is actually the source data (not
> the original data table).  The problem I have is that this intermediate
> pivot table has 3 columns of unique values (with over 1000 rows) before
> we get to the data.  I can't just copy it and make a pivot table, or
> create a mirror sheet that isn't a pivot table because of the blank
> cells underneath each change in value.
> 
> I have this:
> 
> Source   Name   Attempts  Data
> Mail       Joe       1             654
>                          2             700
>              Bob      1             300
>                          2             400
>                          3             800
> Phone   Joe        1             324
>             Bob        1             466
> 
> And need this to pivot:
> Mail   Joe   1  654
> Mail   Joe   2  700
> Mail   Bob  1  300
> Mail   Bob  2  400
> Mail   Bob  3  800
> Phone Joe  1  324
> Phone Bob  1  466
> 
> Do I have any options other than manually entering the repeating data
> in blank cells?  What do I do if I want it dynamic and have the 2nd
> pivot table change when the original data is updated?
> 
> Thanks!

-- 

Dave Peterson
0
petersod (12005)
9/19/2006 9:39:15 PM
That was wonderful!  Thanks!

Dave Peterson wrote:
> You can select those cells and do
> Edit|Copy followed by Edit|Paste special|values.
>
> Then you don't have a pivottable.  (You may want to paste to a new worksheet to
> retain the existing pivottable.)
>
> But after you do that, you can fill those gaps by following the techniques at
> Debra Dalgleish's site:
> http://contextures.com/xlDataEntry02.html
>
> I find the manual technique quicker than the program approach.
>
> CanoAko wrote:
> >
> > I'm trying to make a completely new pivot table out of an existing
> > pivot table where the existing table is actually the source data (not
> > the original data table).  The problem I have is that this intermediate
> > pivot table has 3 columns of unique values (with over 1000 rows) before
> > we get to the data.  I can't just copy it and make a pivot table, or
> > create a mirror sheet that isn't a pivot table because of the blank
> > cells underneath each change in value.
> >
> > I have this:
> >
> > Source   Name   Attempts  Data
> > Mail       Joe       1             654
> >                          2             700
> >              Bob      1             300
> >                          2             400
> >                          3             800
> > Phone   Joe        1             324
> >             Bob        1             466
> >
> > And need this to pivot:
> > Mail   Joe   1  654
> > Mail   Joe   2  700
> > Mail   Bob  1  300
> > Mail   Bob  2  400
> > Mail   Bob  3  800
> > Phone Joe  1  324
> > Phone Bob  1  466
> >
> > Do I have any options other than manually entering the repeating data
> > in blank cells?  What do I do if I want it dynamic and have the 2nd
> > pivot table change when the original data is updated?
> > 
> > Thanks!
> 
> -- 
> 
> Dave Peterson

0
canoako (2)
9/21/2006 10:10:46 PM
Reply:

Similar Artilces:

Globally make all rows As same height/ column As, Bs, Cs (etc) same width throughout workbook?
They're doing their level best here to drive me crazy <g>. I have a spreadsheet with about 18 sheets. Plus I must compare that to another one with a similar amount of sheets. Trouble is, the settings for both _and_ for the individual sheets are literally all over the place! It's hard to get a standardized printout because of this. I'll sort them all which will help but what will do the most good, too, would be to have the columns of identical size as well as row A being the same size for all sheets. The only global change function I know is the page setup when you select...

data entry form for several tables
I have a relational database with the following tables and keys: Birds(BirdID) Events(EventID, based on Date/Time and Site) BirdEvent(BirdID,EventID as foreign keys) Data entry form I am designing has text boxes for BirdID, Date/Time, Site, and various data. Thus the form would be entering data into the BirdEvent table. If it is a new BirdID, will Access automatically create a new record in the Birds table? If it is a new Date/Time and Site, will Access automatically create a new record in the Events table? If not, I know how to write code to check on existing records and write a new...

Adding new records to a table in VB code
I have a rather complicated form that loads several arrays with fields that I need to add to a table. When I go to another form that 'should' add the new records (using a for/next loop) it stops and gives me this message: Run-time error '2499': You can't use the GoToRecord action or method of an object in Design view. I have this line of code right after the 'For' clause: DoCmd.GoToRecord , , acNewRec and just before the 'Next' statement I have this line: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, acMenuVer70 I have used that in an e...

Copying daily data from one tab to a weekly summary table
I have been working on this for a little while now and can't seem to grasp the right formula to use. I have columns of data sorted by name to be tracked daily. I need to then give my boss the information so she can not only see it daily and monthy which I have figured out - but wants to see weekly as well and thats where im stumped. The formula I am using to get the week number populated works great and is =WEEKNUM(Table1[[#This Row],[Ddate]]) What I need to do is get totals from "Table2" to "Table3" into a weekly format instead of daily. So in esse...

new internet carrier & I can't make outlook work
switched internet carriers now have comcast.net cannot send or recieve Outlook e-mail Comcast e-mail works fine, but not on Craigs List In news:E1C044F1-3490-40C5-94CF-E1C997CCA5A2@microsoft.com, Quinton567 <Quinton567@discussions.microsoft.com> typed: > switched internet carriers > now have comcast.net > cannot send or recieve Outlook e-mail > Comcast e-mail works fine, but not on Craigs List What does Craig's List (and web browsing) have to do with your email, or Outlook? Who hosts your email? Comcast? You need to make sure you use their server for your POP server...

why put a string be put in the string table and not a header file?
I have found it is easier to work with string defintitions that are placed in a separate header file (i.e. files with extensions '.h') than placing them in the string table. For example, all error messages can be placed in a file named 'ErrorMessages.h'. If different languages are to be supported, one could create a separate header file for each language (e.g. EnglishErrorMessages.h, FrenchErrorMessages.h, etc). It is easier to work with a simple header file than the string table. Furthermore, using the resource editor to edit a string table is a slow and tedious work, p...

How to make the letters larger in my outlook view
I want the entire outlook 2003 screen letters to be larger, everything on the page. Any help with that? "View of Outlook screen" <View of Outlook screen@discussions.microsoft.com> wrote in message news:412A44E4-736E-4CD9-AEAA-DEFDCFBFEAA5@microsoft.com... >I want the entire outlook 2003 screen letters to be larger, everything on the > page. Any help with that? Change your Windows DPI setting. -- Brian Tillman [MVP-Outlook] Have you tried changing your resolution? -- Kathleen Orland "View of Outlook screen" <View of Outlook scr...

Open a table for search
Using VBA I want to open a table. Then move to first record, then check the data in [name field] and compare it to the data the user has typed into a text box on a form. When it finds a match or the end of file - use a message to inform the user of results. The crude code below will give an idea of what I am trying to do. I have been away from Access and VBA four about seven years. Open table/recordset Move first Do until EOF Read [name field] If [name field] = Me.[text box] then Message “You’ve hit pay dirt” endsub EndIf Move Next Loop Message “Search failed...

Use color or marked cells to make a chart
We have an excel 2002 shared workbook that we use to keep track of our capacity for incoming patients. The cells are color coded for each different insurance. How can i build a chart to show how many of each insurance we have taken in each week. The cell range looks like this. ='2268 SEATTLE'!$B$5:$H$13,'2268 SEATTLE'!$B$17:$H$25,'2268 SEATTLE'!$B$35:$H$43,'2268 SEATTLE'!$B$47:$H$55,'2268 SEATTLE'!$B$65:$H$73,'2268 SEATTLE'!$B$77:$H$85,'2268 SEATTLE'!$B$95:$H$103,'2268 SEATTLE'!$B$107:$H$115 We would like to be able to kee...

Excel Pivot Table
Hi all, I have a pivot table where I created a calculated field that averages three of the fields in the pivot table. My problem is that it averages the "blank" (which are created from blank fields in the source data) fields instead of ignoring them. How can I average the fields in a Pivot Table and not include the blanks in the average? Use =Sum(F1,F2,F3..)/Count(F1,F2,F3...) Blank cells will add nothing to the Sum total, and Count will only count the cells with numbers; hence you can obtain the average for the numbered cells. Paul "Jeff" <Jeff @discussions....

Include field not in table in report?
I'm writing a set of tables and forms and reports to manage a small chess club at my daughter's school. One of the things I create is a report on the previous chess match, with the players as drawn and their match results. I extract this from my Games table. The Games table has a foreign key to the Matches table where I have a record recording the Match data (title, date, time, etc.) When I create the Game Results report, I'd like to include the title of the match and it's date in the header. I created a text box and inserted the following expression: =(SELECT [Title] FRO...

Updating table with information from Form input.
I have a table (Dispatches) with 16 fields. Have developed a Form used to input data for the table. The form is based on a Select query. All data is save properly with the exception of 3 pieces (driver ID number, company driver number and telephone number) The information is shown in the form but doesn't get stored in the table. -- Dave Barnes Dave If a form has a control, but that control's value doesn't get stored in the table on which the form is based, usually you'll find that the control is not bound to the field into which the data should go. Open your form ...

Pivot Table with calculated Item
I have inserted a simple calculation item in my pivot table and now I have lots of rows with zero values, that don't relate to the parent feilds. My table shows total qty ordered in Qtr1 2004 and Qtr1 2005 grouped by Sales Rep, Customer, Product Family, and item code. Once the calc items were inserted, each SalesRep has an entry for every customer, which has an entry for every Prod Family, which would have an entry for every item (if it didn't exceed the limitations). Can anyone help me remove all these extraneous entries?? GD ...

Pivot table question #4
Hello, I have the following pivot table : Continent Country Sales __________________________ Europe France 100 Spain 200 Italy 300 Asia Japan 100 China 200 I understand that "Europe" and "Asia" do not get repeated on lines 2,3 and 5 because they act as a kind of "header" for the countries. But the purpose of my table is to do an extraction, that will be later used for data manipulation. Therefore I need all lines to have all elements, s...

Pivot Table Ignore Blanks
Hello, I think flavors of this question have been posted, but I wanted to ge confirmation on the particular situation. I have a pivottable with category row that I want to aggragate to (in this case, a location) The numerical data is either a value or blank. Is there a way to set u the data so that when the pivot table is counting the number of field in a particular location, it only counts nonblanks? This would b important not only for counting, but also for taking the average ove only the non-blank fields. Any help is appreciated, Ripa -- Message posted from http://www.ExcelForum.com ...

Trying to include all values from 2 tables in Select Query - even where there is no match
I am pulling from a database (that is in very poor shape) trying to determine weekly hours out of a project accounting system with little luck. The problem is, I'm pulling each Friday and then have to subtract YTD Hours from Week 1 out of Week 2 (Current YTD - Previous YTD). The fields I have to work with are Name, Cost Center, and Cost Center Type. In my query I concatenate these values to create a unique field, so that I can link the tables together. Where I run into problems, I have 3 options...Show where they are the same, Show only from table 1, or show only from table 2. Often w...

Look up table help needed
I have never done this before so I need hand holding for this one. I have been thinking about how to do this effectively. I use a program DSS which works with Access and other Engineering programs feed data into it dynamically. I also have static tables (manual) that I input data into such a forecasts. We have several forecasts we use and refer to that plot type curves on graphs so we can monitor production (anticipated gas well production). I have a 2010 forecast in Excel. There are 2 columns, one is numbered 1 - 9329, and column 2 is the daily forecast for that day. My table...

how do i keep adding to existing total in same row
i want cells a & B to be incoming and out going numbers and cell c to total accordingly. i have that part but want a & b cells to be reinputted band total change with new inputs so that total continues to grow or shrink with each new imput want to reuse same a & B cell agian and agian Look here: http://www.mcgimpsey.com/excel/accumulator.html -- Kind regards, Niek Otten Microsoft MVP - Excel "marklessexcel" <marklessexcel@discussions.microsoft.com> wrote in message news:A596C782-1E01-43C4-9474-A384F69630F7@microsoft.com... >i want cells a & B t...

Multiple Record(12 Entries) Into Table w/ Form
I am attempting to send more than one record with one form to the table and I can't seem to get INSERT ... VALUES to enter records separately and the text boxes have different names but the same Control Source so when i change the number all the fields update with the same number when i want each field to have different numbers... You help is very appreciated Hi Graffyn, The method of using insert and values to add data to a table works very well to do one record as you have found. To update several records at a time instead of the values part of the query, you need a select query t...

error when I want to add an existing contact to an account
Hi, It's appear an error when I try to add an existing contact to an account. How can I do to resolve this error "Impossible because there is a cascading relationship " I can add a contact to multiple accounts if I use the 'principal contact' field in the account form. But I need to use the 'add an existing contact' button. Thanks, Rachida This error usually occurs if the record you are trying to add allready has a value for the relation you are trying to add it to. There is no way to change this behaviour. -- Patrick Verbeeten (MCPD) CRM/.NET Consulta...

Pivot Tables #4
When I double click to drill down to the supporting data in a pivot table, the data is displayed on a new tab within the workbook. Then to close that data, I have to delete the tab. Two questions.... 1) Is there a faster way to view then close the data without having to delete a tab every time? 2) If I leave the new sheet open, is it linked to the pivot table? In other words, if I refresh the pivot table, will the data on the new sheet be automatically refreshed as well? Thanx .. #1. I don't think so. But maybe you could apply Data|filter|autofilter to the original da...

Publisher brouchure options, how to make it available, it's grey?
At the brochure section there is a part which have the 3 and 4 panel option, but is grey. I have all downloads up-to-date in my PC for Office. Where do I need to go to get the drivers or software for this feature be available for use. I own Publisher 2002. Thanks. JLCHEOPR wrote: > At the brochure section there is a part which have the 3 > and 4 panel option, but is grey. I have all downloads > up-to-date in my PC for Office. Where do I need to go to > get the drivers or software for this feature be available > for use. I own Publisher 2002. Thanks. ========================...

find records in one table not in another table
hi to all I'm sure this is easy, but as a new user I could use some help. tblAllMembers lists records for all present and past members of a sports club. tblCurrentMembers has the same fields but lists only currently registered members. Both tables have primary key memID. I need a query that lists all records in tblAllMembers that are NOT in tblCurrentMembers. Any help is appreciated. -- Thanks Sophie Create a new query, and select the Find Unmatched Query Wizard. It'll walk you through the process. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e...

Make to Order Items
Hi can somebody ellaborate on how to handle make-to-order (MTO) items in GP and how it is connected in SOP (Sales Order, Sales Invoices etc...). This will be beneficial to all members Thanks Daniel ...

Palette Master table deleted in version 8
We have customized palette (for example, the Sales Transaction Entry is "Enter Sales Orders" etc). In version 8, this table is dropped. The SDK for version 8 just says that it's deleted, but does not state where the data went. WHere are all our customized palette names if the table is dropped? Does this mean we cannot control the names in our terms? This reply is in regards to your later question about renaming menu commands. There is a work around in v8.0 that will allow you to do this. 1. Open the Toolbar Customization window (View>>Toolbars>>Customize....