Excel 2010 :: Pivot Table Grouping

Apr 28, 2014

I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.

Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.

View 3 Replies


ADVERTISEMENT

Excel 2010 :: Grouping Information In Pivot Tables

Oct 24, 2013

I have a pivot table below: Excel 2010. All of the information within it is text, not numbers, as illustrated below as well.

Below is what I have now....

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Column C, Row 2 = Green

What I want it to look like is below, removing the second word "green" from column C.

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green

Is there a way to do this and do it automatically? If I was doing this in a regular excel sheet, I would have a merged cell in column C, rows 1 & 2.

View 2 Replies View Related

Excel 2010 :: Grouping Pivot Dates By Group - Can Change Quarters?

Nov 2, 2011

One cool feature of 2010 is ability to (right click on dates) and Group them into Quarters.

How to change the Quarters to be in line with e.g. Financial Year, as opposed to fiscal years? i.e. have a start date of say June?

View 5 Replies View Related

Excel 2007 :: Pivot Table Grouping And Filtering

Sep 10, 2013

Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.

My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.

View 2 Replies View Related

Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?

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

Excel 2007 :: Pivot Table - Daily And Monthly Field Grouping

Oct 17, 2011

I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.

I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.

Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?

View 1 Replies View Related

Excel 2010 :: (VBA) Pivot Table And Pivot Cache?

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

Excel 2010 :: Create Sheet With Table From Pivot Table?

Apr 26, 2012

Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.

Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)

View 3 Replies View Related

Excel 2010 :: Filtering Pivot Table?

Sep 13, 2012

I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.

View 2 Replies View Related

Excel 2010 :: Pivot Table Sorting With VBA?

Apr 10, 2013

Using Excel 2010

I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.

Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.

Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.

Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.

This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...

HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...

Sometimes we only scan pants for an order, sometimes only tops, sometimes both.

Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?

I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:

XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X

View 2 Replies View Related

Excel 2010 :: Create Pivot Table VBA

Nov 26, 2013

I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".

Code:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Sheets("Raw Data").Range("A1:AG" & Z), _
TableDestination:=Range("A3"), _

[Code].....

View 1 Replies View Related

Excel 2010 :: Pivot Table Showing Zero Instead Of Value

Mar 8, 2014

My problem is this: I use Excel 2010 and have Pivot Tables that are using a Data Connection which is being built (and rebuilt) with VBA. The Pivot Table field is calculating a field that contains an integer value. The Pivot Table field filter properly displays this value.

However, the Pivot Table itself calculated Sum field only displays 0 (aka zero). Other fields that are exactly the same as this one are displaying properly.

My data connection is built as follows:

Code:

ThisWorkbook.Connections.Add "ChartQuery", "", "OLEDB;Persist Security Info=0;DSN=Excel Files;
DBQ=" & wbFullName & _ ";DefaultDir=" & wbPath & ";DriverId=1046;MaxBufferSize=1024;PageTimeout=5;
BackgroundQuery:=False", sSQL, 2
(And yes, my sSQL is sound, there are no typos, no special character issues)

The filter in the Pivot Table shows my value (for example, 14) however it always only displays a zero

My Pivot Table is formatting the field (like the others just like it) as a Number, two decimals
My Pivot Table data source is ChartQuery

The worksheet this is pulling from is also set to use Number format for the entire column, but changing that doesn't make any difference even on the other fields that are working.

View 9 Replies View Related

Using VBA For Pivot Table Grouping

Aug 7, 2006

i have try to find the VBA source to group a range of value (not manual group it)
say example

1-10, 11-20, 21-30 and etc..

i have try to using macro to record my grouping step to a pivot table as below

Selection.Group Start:=0, End:=30, By:=10

but anyway, i think this is not a good idea.

any other suitable VBA code rather than the above code for the pivot table grouping?

View 4 Replies View Related

Pivot Table Grouping

Feb 8, 2007

I have a large spreadsheet, which has a pivot table, this is grouped by Date (Months & Years). However whenever I refresh the table the grouping is removed. I try to re group the date, but I get a message saying "Cannot Group that selection"

View 2 Replies View Related

Sub Grouping In Pivot Table

Sep 15, 2007

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

View 9 Replies View Related

Excel 2010 :: Pivot Table - Value Field - Multiply

Apr 25, 2014

I have a Pivot table showing costs of several types of items.

The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).

So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.

Can this be done in Excel 2010?

I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.

View 3 Replies View Related

Excel 2010 :: Pivot Table Blank Column?

Jan 27, 2014

Why do blank columns with no information sometimes appear when I prepare pivot tables? Excel Version 2010. I hide the columns, but many times they re-appear on screen or print-outs.

View 1 Replies View Related

Excel 2010 :: Hierarchical Display In Pivot Table?

Dec 1, 2011

I tried multiple variations of searches for this, but can't seem to get this to work. I've been asked to perform some analysis on the number of direct reports for each manager in our company as well as the number of total reports. I have been able to calculate this with no problem. My difficulty comes with the request to be able to display this information in a particular way with Excel. (this is using Excel 2010)

What the VP would like to be able to do is to drill down on each manager to see all of their direct reports, and then, for any of those direct reports that are managers, be able to further drill down into those people's direct reports. The difficulty I am coming to is that, depending on the employee, there can be as many as 9 layers of management when I work this all the way up to the Executive Vice President Level.

My data is currently laid out such that my first 5 columns are identifiers such as employee id, employee name, job title and organization information. After that I have columns for the managerial hierarchy. For example first column of this (let's call it column H) is the EVP, the 2nd column (column I) is the Senior VP, 3rd column (column J) has the various reports to the senior VP, 4th column (column K) is the managers that report to those in column J, and so on, up to 9 layers.

For example, one of the employees in my finance area would have name of the EVP in column H, the SVP in column I, the director in column J, and their manager in column L. The remaining columns that could house hierarchy data are blank for them because of where they are in the hierarchy.

When I set up the pivot table, I placed each field of the managerial hierarchy in the row area and then the employee field at the end. The problem I encounter is that with all of the blank cells in the data due to the varying levels of the hierarchy, I get a lot of (blank) fields in the pivot table for any layer below where the person fits in (ie, for the example above, the EVP, SVP, and manager name come in find, but then it is (blank) on down to the employee name.) If I deselect (blank) in the field settings, it eliminates all of the rows that don;t go as far down in the hierarchy, i.e. I deselect (blank) at level 5, any employee that is 4 or less levels from the EVP does not appear.

I also tried doing the same as above but including the employee name in my hierarchy (so for the example above, the employee name would be placed in column M). This worked a bit, but then after every employee was the series of (blank) drill down options.

The ideal solution that I've been asked to come up with would be to have the data display as it does when I've set the pivot table up with the employee name in the appropriate hierarchy level, but not have the drill down (blank) show for those that don't have layers below them.

View 1 Replies View Related

Excel 2010 :: Cannot Create Pivot Table In New Sheet

Jan 21, 2012

I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.

View 9 Replies View Related

Excel 2010 :: Pivot Table Filter Query

Feb 21, 2012

I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.

However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).

Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?

View 4 Replies View Related

Excel 2010 :: Pivot Table Change Event

Mar 22, 2012

I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three Pivot Tables

Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3

When I use In Sheet 2 the following lines of code

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";

How to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.

View 3 Replies View Related

Excel 2010 :: Count Non-blanks In Pivot Table

May 23, 2012

I have a simple pivot table that is types by state. I want to know how many types are in each state. In the attached image the answer for Alaska is 5, for Arizona 5, for Arkansas 1, etc. How do I get Excel to tell me that for each state?

I'm using Excel 2010. I have PowerPivot installed but really don't know how to use it yet.

View 7 Replies View Related

Excel 2010 :: Add Another Data Into Pivot Table Chart?

Jul 30, 2012

Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.

I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.

View 9 Replies View Related

Excel 2010 :: Expanding Subtotals In Pivot Table

Oct 29, 2012

I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.

View 1 Replies View Related

Excel 2010 :: Squared Value Pivot Table Column

Jun 7, 2013

I have a pivot table, organized by industry (with a number of companies under each industry code. I am attempting to calculate the Herfindahl index for every industry, which is the sum of squared market shares of all companies in an industry.

Current situation: I have a column sales and a column market share (which uses the information of sales and shows all items as a % of parent total, which is the specific industry; hence the market share of industry 2821 is 1).

Problem: I am trying to get the market share (so the squared value of the current market share column) of all companies in a particular industry, as I want the sum of these values for every industry.

I am aware that by using Insert -> Calculated field, you can make a formula (market share*market share), but market share is not one of the variables I can select.

Industries
Sales
Market share

2821
2,7586E+11
1,000000

[Code] ......

View 1 Replies View Related

Excel 2010 :: Change Pivot Table Source With VBA?

Nov 25, 2013

I am using Excel 2010 and have a workbook with two sheets - "Risks & Issues" which contains the source data and "Risks - Summary" which contains a Pivot Table called "Dashboard"

I would like to create a command button called 'Refresh Data' which will be on the "Risks - Summary" sheet. When I add a new line to the "Risks & Issues" sheet, I would click the button in "Risks - Summary" and it will update the Pivot Table range.

So far I've tried using some examples found on this site, although with no experience in VBA macros, I'm not really sure what they do; all I know is that they cause an error.

Code:

Sub Refresh_Click()
Sheets("Risks - Summary").PivotTables("Dashboard").SourceData = Sheets("Risks & Issues").Range("A2").CurrentRegion.Address(True, True, xlR1C1, True)
End Sub

[Code].....

View 1 Replies View Related

Pivot Table Dynamic Grouping

Sep 15, 2014

I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..

View 4 Replies View Related

Grouping Data In A Pivot Table

Oct 31, 2008

from the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.

View 4 Replies View Related

Pivot Table Disable Grouping

Jan 3, 2009

I have the attached Pivot table. When i create this pivot table, it groups some of the "A# and Name"s.

e.g.
"A277003 / VICTORIA WILSON"
"A444302 / YASEMIN KELES"

Is there for the pivot table to not group by the A# & Name and to list the A# and names on each row...

Please see Output sheet to see exactly how I would like it?... But i would like this to be done in the pivot table so i can create a macro..... to do this..

View 7 Replies View Related

Grouping Dates In Pivot Table

Jan 9, 2009

I am having issues grouping dates by month in the attached example. The date field is formatted as text. When I attempt to group in the pivot table, I am not given the grouping dialog box. I can't figure out why.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved