To Analysis Big Database- Pivot Table
Jan 8, 2009
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" ?
View 9 Replies
ADVERTISEMENT
May 8, 2014
Data Analysis . I have 7 tables gathered in one spreadsheet but in seperate sheets. Tables have the same columns but different data baceause they come from different locations. Now I would like to create pivot table to combine data from all 7 tables. I read a little and I got to know that relationships is needed between the tables but I can't create it because I don't have unique data in the table. Usually tables consist repeating names, locations, some numerical data.
For example every table consists worker's name. I create pivot table from one of 7th starting from worker's name in row field and put some data in value field. Then I add another workers from another table. I can do it but when I put in row field I have workers from second table under those from first one. And of course data are badly calculated. I would like to have all workers in row field with proper data. It means one column with all workers from all 7 tables an then some their data
I know I can copy all of them into one big table and then create pivot table but this is solution what I wanted avoid. I know it will work but these data can change from time to time and it will be very hard to maintain spreadsheet. When data will change I will have to copy manually .Is there any solution to create such a pivot table from multiple tables sources but with the same column structures?
View 1 Replies
View Related
Jan 10, 2010
I am tasked with putting together an Excel Spreadsheet to analyze our accounts. There are over 80 items for the accounts - about 20 Income and about 60 expenses. These are for various years - actually the last 5 years - by months.
So, I am trying to figure out how the best setup could be done to make this easier for pivot table analysis.
So I have 12 "rows" for each account item for each year.
Maybe I should wait for comments and/or questions before going further.
Just need to figure out how to set up the workbook/worksheet.
View 11 Replies
View Related
Nov 6, 2007
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 10 Replies
View Related
May 22, 2006
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.
View 9 Replies
View Related
Jan 7, 2009
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:
View 7 Replies
View Related
Aug 28, 2013
i have a excel file which has a formatted pivot table which displays customers by country in row A 2 down to 36 with approx 36 customers in the values field i have volumes by alarms and tickets along the column labels it shows the previous 7 days with date and then the alarms and tickets in each row. My problem is i am trying to find the code or location where i can change the only bring back 7 days data to another value. i have searched all connection properties and definitions. I know there is a value somewhere that allows you to change this number to say from 7 days to 30 days. If you click on the column labels for date it shows the dates back 3+ years but it still if i tick more than 7 days only show 7 in the table.I have image if needed
View 1 Replies
View Related
Sep 9, 2008
I have attached a spreadsheet of an example of a database with a pivot table and a desired report
Can anyone give me a pivot tabe from my database that looks like my desired report.
I am willing to seperate the database into two seperate databases with different transaction types if necessary.
View 6 Replies
View Related
Mar 20, 2009
I have created a Pivot table as below:
Milk 1 Pint Whole
JAN FEB MARCH APRIL etc
Shop 1 10 12 14 20
Shop 2 8 7 6 2
Shop 3 10 10 10 10
Shop 4 20 30 40 50
Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.
View 7 Replies
View Related
Mar 19, 2013
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.
View 3 Replies
View Related
Jan 4, 2010
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
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
Jan 22, 2007
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies
View Related
Nov 5, 2008
I want to accomplish something like this but slightly different:
[url]
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...
View 6 Replies
View Related
Apr 29, 2006
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.
View 2 Replies
View Related
Aug 19, 2013
I have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.
The structure for each row/record is:
col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)
I want to rearrange the data as:
col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units
Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?
View 5 Replies
View Related
Dec 13, 2006
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
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
View 4 Replies
View Related
Feb 28, 2014
I'm opening a non-excel file in excel and it formats it into a series of rows/columns. The problem I have is that it doesn't consolidate all related data in the same row, for each new column of data it breaks into a separate row. I'd like to easily roll it all up into a single row.
I've attached a sample file to illustrate. The "Original" sheet is what I'm starting with and the "Final" sheet is what I hope to condense the data into.
View 1 Replies
View Related
Nov 22, 2006
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 Replies
View Related
Aug 16, 2013
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
View 3 Replies
View Related
Sep 18, 2008
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.
View 9 Replies
View Related
Jan 23, 2014
I have a pretty vast database which I populated through VBA from a certain number of worksheets. I now need to create a table which shows min, max and average for each location given x, y or z condition. So far I only approached it by creating a secondary table with each range address and then using INDIRECT, but this would require extra work on the database (insert a function to sort out locations alphabetically and within each Range.Location sort out by conditions (note. A, B and C are not in alphabetic order)) and is also very ugly.
View 3 Replies
View Related
Jan 28, 2009
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.
View 10 Replies
View Related
Feb 1, 2006
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.
View 3 Replies
View Related
Jul 7, 2014
I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
View 1 Replies
View Related
Jan 7, 2014
I'm not grasping the Pivot Table correctly. I've written code to create a sum of values based on a worksheet. Specifically:LocationIDDeptSum of Hours Worked. Location, ID and Dept are rows. This effectively provides the aggregate values that I need based on the row groupings.Here's where this is falling apart. I need to create a new worksheet based on these values. I assumed the three row values - Location, ID and dept - would be in a hierarchy. It's possible they are, I just can't figure out the object model.
When I loop through the PivotItems collection of the PivotFields("Location"), I get what I need. However, I'm unable to determine how to loop through the child values (just for that location). PivotFields("ID") returns all IDs. I can't figure out how to return only the child entries for each pivot item. GetPivotData hasn't been very useful for this. As far as I can tell, GetPivotData, while its return type is listed as Range, throws an error when more than one cell is returned. Worst case, I suppose I can just parse the data in the DataBodyRange of the pivot table - maybe not, I haven't tried that. I'm hoping there's a way to iterate through these collections, but based on what I've seen from Google searches, there may not be. Does my pivot table need to be rearranged? I suppose I could also just dump this data into a data table
View 1 Replies
View Related
Mar 13, 2014
My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.
New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.
Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.
My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.
Fix / Solution:
Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.
Code:
Dim VBAPPPC As PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable
[code]...
View 1 Replies
View Related
Apr 20, 2014
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
View 1 Replies
View Related
Jul 25, 2006
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
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
View 6 Replies
View Related
Sep 5, 2006
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
View 3 Replies
View Related
