Inquire Split Database With Pivot Table
I want to build a pivot table on database that is not centralize in one place.
every month the new data is insert in a new sheet (of course with the
same fields names)
But the wizard, refuse to build a pivot table from data that is not
concentrated in one place.
(My intention is not the option: "Multiple Consolidation Ranges".)
View Complete Thread with Replies
Related Forum Messages:
Pivot Table, Charting Or Access Database
I have an excel spreadsheet in which I must sort the data by program and then activity code. From that I need to know the number of consumers by activity code and their total units. I must then graph this by program. I have been debating on whether to use an excel pivot table, break the data in to multiple spreasheets to then graph it or shoud I upload the file into access.
To Analysis Big Database- Pivot Table
I have a spreadsheet with 15,000 rows x 15 more columns, i would like to find out the number of delivery for each single order.
The database similar as below :-
Order No. Delivery Date Product
12345678 01.01.2009 TV
12345678 01.01.2009 DVD PLAYER
12344568 02.01.2009 TV
14567892 03.01.2009 RADIO
14567892 04.01.2009 RADIO
14567892 31.12.2008 BLUE RAY
15465879 06.01.2009 MP3
1)what excel function should I use in order to find out the number of delivery since there are so many duplicate records in the database.
2) Shall I use "Pivot table" ?
Update The Source Data For The Pivot Table In Database
I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:
Split Pivot Table Field Into Seperate Sheets & Not Allow Drilldown
My goal is to macro the creation of pivot tables into seperate worksheets based on each particular record in the "Office" (location). Ultimately, I will apply an email macro to send out each pivot table (and source data if necessary) to various recipients. I need to make sure that each recipient of a pivot table is limited to only drill into the detail for their particular "Office" and not able to view other location's information in the Source Data worksheet--is this possible, or do I need to create separate source data worksheets for each Office's pivot table in order to limit the viewing?
Items I need guidance on:
Creating a macro to breakout pivot tables into seperate worksheets based on "Office" locationPlease advise on how I can secure Pivot Tables so that the user can only drill into the information originally presented in the pivot table they receive and will not be allowed to view all of the source data.
Please find attached a file with my source data and an example of a pivot table for one of the Office locations (Chicago).
Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
Create Database Table From Multi-Column Summary Table
I want to accomplish something like this but slightly different:
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
Split Data In A Cell And Get The Value For The Split Data From A Different Table
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
Months To Be Sorted In Ascending Order In Pivot Table, Want To Use Multiple Colors In Pivot Charts
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
Database Functions Vs. Array Formulas Vs. SUMPRODUCT Vs. Pivot Tables
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Refresh Pivot Tables Linked To Pivot Table
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
How To Mimic A Pivot Table Without A Pivot Table
I have a list of items and their associated quantities, many items appearing multiple times. I need a concise list that summarizes each item and sums all of its quantities.
The obvious solution is a pivot table. However, I update this list frequently and for some reason the pivot table is difficult to update. is there a function or simple vba code that I could put into this workbook that would work better than my unflexible pivot table?
Split Table And Import
I have a Table in my schema (Oracle Database) that has about 250,000 records (rows in excel speak), and we use Excel 2003 at work, so I can't import this there and then split it by Store Number (column).
Each Store number has less than 65K rows, so it should fit into a sheet.
Has anyone written some VBA script that will be able to look into my SCHEMA, and the Table in question (specific name) and then split it by Store number and import each store to it's own sheet.
So if the Table has 20 stores, there would be 20 sheets inside the workbook.
Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
pt.ManualUpdate = False
My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
Import Data From Access Table To Pivot Table - Enable Auto Refresh
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
Security In Table/list Database
Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record.
Automatically Convert Table To Database
I've posted an example workbook that has sheet1 as how the data comes and sheet2 as what I need it to look like (through some kind of automatic process). I really don't have a clue as to how to get this done, and I'm guessing some VBA is going to be required.
Apply A Filter In A Pivot Table And Extract Results In A Table
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
Pivot Table An Extract Of Each Data Contained In This Table
i have a pivot table an extract of each data contained in this table.
[img]Count of NAMdate
Pivot Table: Table That Shows As A Header The URL
I want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited.
Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.
Copy & Transpose Table Data Into Database Format
I have a sheet with products down the side, and dates across the top. Numbers will be entered at an intersection between dates and products to represent quantity. Whenever there is a non-blank value for the quantity, I need to copy the products, dates, and quantity into a traditional database format. I have attached the original data format I get from suppliers on when they will deliver, and then show a separate tab for "Desired Result"
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.
So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.
This solution also worked perfectly, but now I've been told to create the graph without macros.
Does anyone have any suggestions? The requirements/details are as follows:
1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels
Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
Database Named Database And Return A Time In Column
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
% In A Pivot Table
I am trying to add a column to a pivot table that shows the percentage of another column.
I have Sales and Returns listed for my company's products in the pivot table columns.
Lets say the numbers are: Sales are 10 and returns are 4. I show both columns and want to add a 3rd column that shows 40% (4/10).
Does anyone know how to do this in the pivot table? There are plenty of percentage options, but none to do this simple one.
Value 0 In A Pivot Table
This dataset I have has empty cells, but there is a formula in these cells.
When I make a PT from that dataset, the cells which are empty in the dataset do show up in the PT, as zero's.
Say in the first column of the table are employees, in the second column is a number that is of interest. I want the employees with a number of interest that has value of zero not to show up.
Is there a way to leave those rows out of the table?
ERROR "The Microsoft Office ACCESS Database Engine Cannot Find The Input Table"
Using a pivot table, I'm linking to an external data source in ACCESS. I use the pivot table wizard, select the database (spend2008.mdb), and then ONE table that is not in a relationship/linked etc, its just a plain table, and then I get the error
"The Microsoft Office ACCESS database engine cannot find the input table or query "mdb.summary". Make sure it exists and that its name is spelled correctly".
This is new spreadsheet, so new join to the data. I'm selecting the table from the Query Wizard, so not possibility of a spelling mistake. I've been all through the database to make sure I haven't set a link or relationship. There are a total of 4 tables in in the DB that are related, but I've deleted the relationships
Pivot Table - Subtotals
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.
clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal
Lets say I have a list like this and I want to count the number of clients (3) or countries(2).
I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
c - 4
Pivot Table - Percentages
I am trying despeartely to finish this out. Here is the deal. I created a pivot table (see attached). The issue is that I need the numbers in the red boxes to be a percentage of the total number below - so the 2 should be a percentage of the 9 (22%) and the 3 should be 100% and the 7 should be 78%. I cannot seem to get this to work. Also, there are multiple rescue groups that need this and each needs to be the % of its own total number of animals.
Pivot Table :: How To Get The % Data Above A Particular Value
I have a excel sheet with following 4 columns
Transformer earth resistance:
Now how can I get answer of following queries in Pivot table. (Excel 2007)
1. % of a particular capacity of transformers in a division say % of 400 capacity transformers in all divisions.
2. % number of transformers having Transformer earth resistance value above a particular value in all divisions. Say % of Transformers having Transformer earth resistance above 2.6 in each division.
Sorting In Pivot Table
I have created a pivot table that contains the following data:
Date as in 5/1/08, 6/1/08, etc
With counts in the cells
I can sort on Country name and get an alphabethical list by country name showing teh numbers for each date
However I want to sort by the largest to smallest number that shows in the date column 01/01/08
Is there a way to sort on that colum-while moveing all the numbers in the other date columns along with it?
Count In Pivot Table
I have created a pivot table from a spreadsheet that had around 27 rows for each employee (i.e: each paycheck the employee received). The pivot table turned out great, but I need to know how to make it count how many employees are in each department and show it in the table.
Alternative To Pivot Table
I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.
In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..
Pivot Table- Sumif
I was wondering if any one could guide me how to use SumIf or any other functions while creating a pivot table from excel data in a very presentable manner.
The data shows - sales in qty to various customers for the month of may. Important columns need to be reported are Item, Qty.
On the second tab (in the attached sheet) i am showing the format how i want. I need to sumif by item code and put the total in the respective company column. I could manually do this by SumIf but if I want to do this for month by month for 4 months then how would i do it quickly.
Is there an easy way report for 4 month's comparative figures to make an analysis to see whihc company orders whihc product and the so on.
Updating PIVOT Table
I have created a dashboard based on a number of pivot tables. (Using GETPIVOTDATA tags)
One of the fields in the Dash tab is Week which is a drop down menu of 1-13. (GLA Dash C3)
I have two pivot tables in seperate tabs. (BottomQuartile1 and BottomQuartile2) both have Week as Report Filter.
At the moment when I change the week in Dash from 4 to 5 i have to manually go into the two BottomQuartile tabs and alter the week to match.
Is there anyway I can automate this process so that when i update the week in Dash it will update the Week in the other two tabs?
Dynamic Pivot Table ....
I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:-
I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's?
More Than One Column In Pivot Table
my table has the following fields: Zone (north, East, west, south), depots in each zone (D101, D102 in North and D201, D202 in South),product code (S101, S102...S100) then sales data for three stores 1, 2, 3.
My original table has the depot code, zone it belongs to and sales of each of the product made to each of the store per depot.
In the Pivot table, I shall need to show Branch and the Product codes on the row-side, and require store codes 1,2,3 to appear on the columns. The data area thus needs to be a summation of D101, D102 for North (for each of the product-codes) and D201, D202 for the South region.
I am unable to display 1,2,3 as separate columns.
Pivot Table Error
Upon opening an existing file, I receive the following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems.
I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error:
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Pivot Table Update
I've created a pivot table that summarizes sales and gross profit on a quarterly basis and YTD basis by Market and store. I have a drop down in my source data file which changes the month. I go to update the pivot table and it no longer recognizes the data fields because the headings change to the subsequent month. Is there a way to not have to recreate the pivot table every time a run a new period? See below. Once I run August and then refresh the data it will not recognize these fields any longer.
Pivot Table Ranges
I have just created a pivot table, however, i have just put the range to the cells that have data in them. The problem is, is that i want to make the cell range bigger, but the cells that i want to include do not have data YET, but will in the future. I have tried creating the pivot tables including the cells with no values, but it ends up puting 1 into the pivot tables cells for some reason.
Sub Grouping In Pivot Table
The attached is the format of a pivot table i have made from a data. MY primary group is the REGION inside the region i want to group as per the LOCATION HEAD.
This report automatically sorts as per the location id.
How is it possible under region i want to group according to the location head and total. i DONT WANT TO CHANGE THE SEQUENCE OF THE COLUMS AND THE FORMATTING
Too Many Items For Pivot Table
I have a database table with selling prices. I want to get a count of number of customers by sales price range.
My nose says this is what a pivot table should do but there are too many sales prices and the pivot table craps out. How do I get them into ranges as above and then count them?