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.
I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers.
I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
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 RelatedI am attaching a worksheet which shows what I would like to see. I am having difficulty writing the formula. The database I have is a list of forex trades. Each trade is made at a specific time, and involves a specific currency pair (such as EURUSD). I want to summarize the trades in a table which sums up a week's trades for each pair. (see worksheet).
View 5 Replies View RelatedI have several data sets that that have the same column headers (product portfolio data, split by categories into different workbooks) and the same structure and want to merge them to create summaries in a pivot table that I can slice the way I need to look at the data (e.g., by "Category 1" level, "Category 2" level, etc..). The messy way would be to just copy/paste all data in one master set and then create one pivot from it. The problem is that this a) takes a lot of time since I have several data sets and b) the content of these data sets is constantly changing, so I would need to constantly update the master data set by replacing updated sub sets.
I was hoping that I can use PowerPivot for that which is apparently not the case - as I understand PowerPivot is only being used to link data sets that contain a column with an identical key to then basically do on a larger scale what a vlookup would do. Is there a way to do this with PowerPivot?
I have included some example data and what I need to be able to show in the attached file.
I have tried using pivot tables but need to keep the totals static when I hide a column.
Can anybody suggest another way of achieving what I need? I need the solution to be dynamic (like a pivot table) but need to hide certain data and have totals including the hidden data.
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" ?
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".)
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 RelatedI use INDEX and MATCH equations quite frequently now and they are invaluable! However what I'm trying to do now doesn't quite fit. I've attached a small sample of my workbook. The sheet called 'data' has three columns of data that I need to split into a grid (called summary). I'd know how to do the opposite of this, as in changing from a grid to a list by using an index and match. But I'm not sure how to do this the opposite way around?
View 3 Replies View Relatedi 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 RelatedMy pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.
The small attached sample shows what I would like.
When i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row.
For example:
Rather than the Pivot table appear like this:
Q108A 72
____B84
____C68
____D5
Q109A 64
____ B 73
____C58
____D 5
I would like the privot table present like the following format:
____ A_ B_ C_ D_
Q108 72 84 68 5
Q109 64 73 58 5
Is there setting I can change, so that the new field is added to a new column.
I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
View 2 Replies View Relatedsummarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies View RelatedI have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?
View 3 Replies View RelatedMacro or formula to build a table from a data.
The data is in the following format in 4 columns: A (Customers' names), B(type of transactions: invoice,payment, Credit,Check), C (date), D(Amount).
Data is for the whole year by monthly.
I would like to build a table like this:
Column (A) 10 names of customers and in next 12 columns by month amounts and types of transactions.
I have 29 excel files with some number of worksheets from 1 to 4. The name of the worksheets are the same in all the spreadsheets. Then I've a got a pivot table. I have to compare some data (3 columns) from the pivot table to the numbers from all these sheets from 29 excel files.
How to do it in a most efficient way?
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 need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.
View 4 Replies View Relatedattached 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 RelatedI 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?
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"
View 2 Replies View RelatedI have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.
Sub AddPTFields()
Dim PT As PivotTable
Dim PF As PivotField
Dim FldName As String
Dim LastCol As Integer
Dim TxtStr As String
Dim wsName As String
[code]....
I have a pivot table that shows the following:
ID_LOCATION1_LOCATION2_COUNT
1__XXAT______213_______1
_____________215________1
_____________226________1
2 XXAL______213_________1
_____________228________1
3 XXAA______213________1
_____________123________1
_____________258________1
_____________065________1
...and the list goes on for 1300 more ID items
What I need is to reformat it to show the location2 as columns one next to each other as follows:
1 XXAT 213 215 226
2 XXAL 213 228
3 XXA 213 123 258 065...
I have two queries regarding pivot table.
I would like to include additional columns with formula in to a pivot table
I would like to get ride of the Items in a pivot table with Zero values without editing the source data.
I have attached a work file in to the below link, [URL]....
I want to get a subtotal of columns B C and D in a pivot table. I have tried to add a calculated item to a pivot table to add columns B C and D. When I try adding a calcuted item I am getting an additional column inserted after columns B C and D. Each additional column has the previous column duplicated. I want a subtotal of column B C and D. I don't want to use the grand total function because I also have columns E through H that I don't want in the subtotal.
How can I get the columns B C and D subtotaled within the pivot table?
How can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.
View 1 Replies View Relatedis there anyway to arrange pivot table columns without manipulating the field list items?
in other words, in older versions of excel, you could just right click the column and select move left, or move right