Reverse Pivot Table :: Recreate The List It Was Derived From
May 14, 2009I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.
I added a sample to show what I mean.
I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.
I added a sample to show what I mean.
I need to extract data from a spreadsheet given to me by my client, the best way to describe what I want to do is "a reverse of a pivot table"
The source sheet is arranged as follows:
Column A: Employee name every 3rd row
Column B: Normal Time on row 2, time and a half on row 3, double time on
row 4, normal time on row 5, time and a half on row 6 etc etc
Column C to I inclusive: Number of hours worked (on that day)
Column J: Subtotal of the 7 days (columns C to I)
Column K to Column DZ: Repeats of Column C to J
Row 1: dates in columns C to DY (except for the weekly subtotals)
I have many spreadsheets like this that I would prefer to have the information in a simple list format with the fields: Name, Date, Rate
Class (Normal Time, Time and a Half etc), Hours. Is there a simple/quick way to extract this information into the above mentioned fields? Am i being stupid and missing a simple solution?
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
Attached is .zip for further clarification.
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View 7 Replies View RelatedI have a pivot table with a group # on the left, and team members on the right. There are multiple team members in each group, and each are on a separate line in the pivot table, like this:
Group #Team1chrisdawnsally2cassiechrisdawnkathysally3jimjoejohntomGrand Total
I need to find where "sally" is on the team (easy to do"), BUT I need to display the rest of the team members in the group with her, like this:
Group #Team1Sally, Chris, Dawn2Cassie, Kathy, Sally, Dawn, ChrisGrand Total
Using the concatanate function to put them all into one cell and then filtering for "contains" was all I could come up with, but there are 14,000 groups
on the pivot table field list-enable on the right hand side of the screen, only after that the needed fields can be dropped in.
Is there any other method to draw the pivot table?
I've a Pivot Table which i would like to get a list of it.
It is from an OLAP cube from Oracle
Based on the image below, every month i refresh this PT, there will be an additional WK. Lets say WK22.
How do i export the whole list from WK08 to WK21 everytime after i refresh my PT?
How do i select the last item in the Page field? If there is WK22, den it will auto select WK22. If not WK21.
Originally I considered posting in the Excel / Email area but I know how to automate the email part of this already once I get the data collection I'm looking for. Let me explain. I have a pivot table that consists of reps, accounts, items, and sales data in that order.
For each rep I want to create an email of affected accounts and items with thier respective sales data. Because each rep has a variable number of lines I'm having trouble defining the exact range. I've used a few pieces of code taken from various posts from this forum to start me off but nothing seems to be working, else I'd post what I already have.
The field list does not appear when I create a pivot table in Excel 2007. It works properly if I start Excel in safe mode. I have toggled the field list button in the PivotTable Tools show/hide ribbon and I tried repairing Office 2007 from the control panel.
I am trying to write code so I can control the PivotFields on a PivotTable from another sheet in the book. I cannot figure out how to get Excel to return a list of the fields in each page field. If I record code to change the Animal PivotField to show dogs, i get the following:
ActiveSheet.PivotTables("PivotTable1").PivotFields("Animal").CurrentPage = _"dogs"
I can also choose cat, birds, and beavers, but I do not know how to get excel to return a list of the option in the PivotField.
I am trying to round a number to the next half penny. The mround function would seem to work but does not work for fractions.
View 2 Replies View RelatedHow can I change my Pivot Table Field List to produce Sum of the values rather than Count of Values?
View 1 Replies View RelatedI have a Pivot Table with pivot fields and data. I would like, through VBA, to get the list of values that can be chosen from a given pivot field.
For example, a list would be Product1 / Product2 / Product3 .... I would like to read that list and put it into a drop down list in a form.
GOAL: I have several Pivot tables on the same sheet with similar fields and I want through macro to allow the user to update them all with one click.
Im trying to create a 'drill-down' interface with the GETPIVOTDATA command.
I believe (but im not sure) that this will require several different formulas.
e.g., assuming this formula resides in A1, this returns all data in the pivot $A$6 for Monday of 6/1/2009:
=GETPIVOTDATA("Sum of Mon",$A$6,"Week", DATE(2009,6,1))
however for cell A1, if the user wants to drill down, then the required formula expands to the following..in this case we are drilling down to Name=Baby Becket/Ball, Stage=Infant..and so on..
=GETPIVOTDATA("Sum of Mon",$A$6,"Name","Baby Becket/Ball","Week",DATE(2009,6,1),"Stage","Infant","B/L","B","WL",)
Essentially, without writing a bunch of IF's in the formula for A1...is there a way to put these formulas in a lookup table, and then depending on what the user chooses on how they want to analyze the data (e.g., they may select Name, Stage, etc from a drop down list elsewhere on the sheet), the appropriate formula is populate in A1?
In a nutshell: Can the formula of a cell be changed depending on what the selection value is of another cell or list value?
This could probably been done easily via VBA, but if there is a formula or vlookup based solution that would be easier..
Is there any code or way to generate the compete list of fields that are selectable from the " PivotTable Field list"?
I have various cubes I need to check and compile the list of fields for each.
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap
1.System data populates sheet
2.Pivot table pulls info I need.
3.Lookup list cross references hand entered data sheet and puts value next to pivot table.
4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies View RelatedI want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?
Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
Using Excel 2013,
I clicked on a field in my RowLabels
I then clicked on the Filter Arrow for the RowField
The SelectedField prompted with the correct field of 4 possible fields
However the item I am looking for is not in the list but I can plainly see it on the screen.
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
I'm trying to place a pivot table in an existing sheet in order to have a list of names next to a P&L. I've got several criteria to filter the names, but when I apply the filters to the pivot table, not all the people show up. The weird thing is that the total at the bottom of the pivot table, which is a simple sum of time in a given month per person, calculates the correct number as if all the employees are there.
When I do the same table in a fresh sheet, in the same file, it shows correctly. It's only when I try to put the table in an existing sheet that it abbreviates the list.
I've written a list and I want it the other way around. Top at the bottom, bottom at the top.
There's no sortable column in it. If it gets sorted it'll be out of order. I just want the same order but reverse the whole thing.
I've looked around a lot and failed to find it so far.
I've got to do is add a column at the side, number it in order then sort on that column, remove it later, right?
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
I have a question on paste special. Is there a way to paste special reverse the order of numbers?
I have data going in reverse chronilogical order vertically on my spreadsheet. So for example,
3/1/07 12.3 ( Cell A1)
2/1/07 13.2 (Cell A2)
1/1/07 14.5 (Cell A3)
12/1/06 16.7 (Cell A4)
I want to now paste this data vertically (so transpose which there is a handy check off box for) BUT ALSO to Reverse the data so vertically it now reads :
16.7 (in cell A1 for ex) 14.5 (In cell B1 for ex) 13.2 (In cell C1) 12.3 (In cell D1)
Is there a way to paste special reverse the order of numbers?
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies View Related