Free Microsoft Excel 2013 Quick Reference

filter pivot table with multiple values?

Hi all,
Is it possible to filter a column pivot table with multiple values without having to manually checking each one off? The pivot table contains about 1000 employee IDs and would like to filter out about 50 without having to go through the entire list? Does this need to be done with a macro? Would it also be possible to filter with a 50 employee IDs on another sheet? Hope this makes sense. TIA!


I am trying to create pivot table with multiple ranges because ther is too
much data for 1 spreadshhet and I don't want to consolidate. I am selecting
datas from 3 different tabs and all are in the same formate with 7 columns
but have differenet numbers of rows.

when I select the ranges and create the pivot table the only options I have
to put into the Page header and body are Row, column and Value. This isn't
flexible enough I wanted to be able to slect from all the fields in the data
ranges.

Is there a solution? Have I done something wrong?

Thanks
Rick

How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.

Hi,
Sorry but I have gone through all help programs and cannot find how to make
a pivot table with multiple worksheets.Any help would be great.

Hello,

I'm trying to change the filter of my pivot tables with a value in a cell.
This is working, but I think it is a bit slow.

And with this solution I can not put the pivot tables above each other. And that is what i want.


	VB:
	
 
Sub Macro3() 
    Dim jaar, maand As String 
     
    With ThisWorkbook.Worksheets("Dashboard") 
        jaar = .Range("C2").Value 
        maand = .Range("C3").Value 
    End With 
     
     
    On Error Goto Errorhandler1 
    ActiveSheet.PivotTables("Lijn1").PivotFields("Jaar").ClearAllFilters 
    ActiveSheet.PivotTables("Lijn1").PivotFields("Jaar").CurrentPage = jaar 
     
    On Error Goto Errorhandler2 
    ActiveSheet.PivotTables("Lijn1").PivotFields("Maand").ClearAllFilters 
    ActiveSheet.PivotTables("Lijn1").PivotFields("Maand").PivotFilters.Add Type:= _ 
    xlCaptionEquals, Value1:=maand 
     
    ActiveSheet.PivotTables("Lijn2").PivotFields("Jaar").ClearAllFilters 
    ActiveSheet.PivotTables("Lijn2").PivotFields("Jaar").CurrentPage = jaar 
     
    ActiveSheet.PivotTables("Lijn2").PivotFields("Maand").ClearAllFilters 
    ActiveSheet.PivotTables("Lijn2").PivotFields("Maand").PivotFilters.Add Type:= _ 
    xlCaptionEquals, Value1:=maand 
     
    ActiveSheet.PivotTables("Test").PivotFields("Jaar").ClearAllFilters 
    ActiveSheet.PivotTables("Test").PivotFields("Jaar").CurrentPage = jaar 
     
    ActiveSheet.PivotTables("Test").PivotFields("Maand").ClearAllFilters 
    ActiveSheet.PivotTables("Test").PivotFields("Maand").PivotFilters.Add Type:= _ 
    xlCaptionEquals, Value1:=maand 
     
    Exit Sub 
     
Errorhandler1: 
    MsgBox ("Het jaartal is niet bekend.") 
    Exit Sub 
     
Errorhandler2: 
    MsgBox ("De maand is niet bekend.") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Does anyone had a solution to have the code in such a way that I can put my pivot tables above each other?
And does anyone know how I can make the code faster?

Thanks in advance!

I've created a pivot table off of data from two other pivot tables. One
pivot table has $millions by product by quarter, and the other pivot table
has volumes by product by category. The first table only has some products,
but the second one has every product. When I create a pivot table with
multiple ranges referencing these two tables and have the values multiply, if
there's no entry in the first table it assumes 1 instead of 0 and returns the
total volume. Is there any way around this, or some other tool in Excel that
would accomplish what I want? Any suggestions would be greatly appreciated.

Thank you!

I have a spreadsheet with two tabs of data. 63,000 rows on the first tab and 28,000 on the second tab due to the Excel 2003 data row constraints. When I try to create a pivot table using multiple sources of data I get an error saying it is too much data to be processed. Is there any workaround to this? Also, I have 12 columns of data on each sheet, each cell with less than ten words. Data is sensitive so I cannot attach.

Thanks.

Hi
I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '

The ranges on each of the worksheets will be the same. Range("A2:Av48")

Any help or directions to other references will be gratefully received.

Thanks

hello,

i have the following problem :

i am using extremely large excel sheets with specific data in it.
i can report approx 10 weeks into one sheet before it is full. then i
need to use a 2nd sheet.

i want to combine those sheets into 1 pivot table, but the layout of
the sheets is exactly the same (and that should stay this way)

when creating a pivot table with multiple ranges i get an error
message that there are similar column names. is there a away to allow
this and to combine those columns ?

for example in sheet 1 there is a lot data from week 1 to 9.
sheets 2 is containing data from week 9 to 19.

so for week 9 there data in both sheets.
i want to create a pivot table that combines this.
so 1 column name with week 9 and from there the calculation from both
sheets.

is that possible ?

thanxxx a million !!!

Hi, I am new to pivot table. I created a pivot table with the
"customer ID" as my row field, and "date of the month" as my column
field. Then I selected two fields as my data. One field is the
category of the things the customer bought and the other field is the
price associated with the item bought. Then in the data drop down I
can see these two fields. However, whenever I deselect one field, the
selection drop down disappears, my table then has only one data
dispalyed and I can't see the other field unless I add that field back
in. Is there a way to keep the selectiond drop down there, so I can
pick to see what I didn't select to see before? Thanks in advance.

Hello All,

I've exhausted the internet and my own abilities to obtain a solution to my VBA issue. I am attaching the spreadsheet and the VBA that I already have completed. A brief explanation of what I am trying to do: I have a dump of information listed on the "DtDump" tab. The pivot table that uses that information is on the "Main" tab. I am running a For Each loop off of the list on the "ID" tab. I am using Brand ID (Column M in ID tab) as the page filter for the pivot table. If Supplier ID (Column L in ID tab) is 0, that means that I want to filter for the Brand ID in the pivot table and ALL of the suppliers that are related with that Brand ID. If the Supplier ID is anything other than 0 I want to filter for the Brand ID and then do another filter in the Supplier ID portion of the pivot table for that Supplier ID. Where I am having trouble is when I need to filter for ONE Brand ID and MULTIPLE supplier IDs. For example, on the ID tab, Brand ID number 80 has Supplier IDs for 38 and 1045. I am trying to figure out how to create variable names for those two Supplier IDs so I can then filter the pivot table by those two variable names. What I have so far, and I am hoping I am on the right track, is this:

'Loop through specific Brand IDs/Supplier IDs on ID worksheet
For Each x In srng

If x = 0 And x.Offset(0, 3) = 0 Or 1 Then
x.Offset(0, 1) = BrandID

CountBrands = PT.PivotFields("ID").PivotItems.Count
CountID = CountID + 1

Main.Select
PT.PivotFields("ID").CurrentPage = "BrandID"
'PT.PivotFields("SUPPLIER_ID").CurrentPage = Supply.Value
PT.ManualUpdate = False
PT.ManualUpdate = True

'PT.PivotFields("ID").CurrentPage = x.Value
'PT.PivotFields("SUPPLIER_ID").CurrentPage = Supply.Value
'ActiveSheet.Calculate

'Set CurBrand = Range("A16")
Set BrandName = Range("B16")
Sheets("DtDump").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:L1").Select
Selection.AutoFilter
ActiveSheet.Range("DumpRng").AutoFilter Field:=5, Criteria1:=BrandID

Dim i As Integer
Dim RowNum As Long
ElseIf x 0 And x.Offset(0, 3) > 1 Then
x.Offset(0, 1) = BrandID
x.Offset(0, 3).Value = OffsetDown
x.Select
ColNum = ActiveCell.Column
RowNum = ActiveCell.Row

For i = RowNum To RowNum + OffsetDown
Cells(i, ColNum).Select
Selection.Offset(0, 4).Value
Next i

CountBrands = PT.PivotFields("ID").PivotItems.Count
CountID = CountID + 1

Main.Select
PT.PivotFields("ID").CurrentPage = x.Value

The bold area is where I am hung up. I am trying to create a loop; if there are 4 individual supplier IDs I want to be able to name each of the 4 a variable that will then go to the pivot table and select only those 4. Can anyone please help? Thank you SO MUCH in advance.

Cheers,

Jim

Hi,

I am provided with a pivot table which has about 25k rows of data the majority of which are not relevant to me.
I am trying to create a method of filtering the pivot table based on a list of codes held in a second sheet rather than manually selecting the 30 of so codes that I require.

I have tried this snippet - it works but i'm not sure how to get the case to look at a range on the other sheet.

Does anyone know a better method of filtering the pivot based on the values on another sheet or help me modify the code to select the case criteria from another sheet?


	VB:
	
 
For Each pi In _ 
    pt.PivotFields("Code").PivotItems 
    Select Case pi 
    Case "Code 1", "Code 2" 
        pi.Visible = True 
    Case Else 
        pi.Visible = False 
    End Select 
Next pi 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many Thanks,

Oli

Please help.

I have a pivot table with multiple fields and I want to sort the whole table by specific dates. I drag the date field to report filter and try to select multiple dates using the "select multiple date" check box.

It does not filter by the selected dates. It will select by all and will select 1 individual date but not by the check boxed dates.

the drop down field always says all when I select mutiple dates.

Any ideas?

Rooster

Hello!

I am trying to filter a pivot table using hover via a hyperlink. I did post about this yesterday but have revised my method.

I am using a hyperlink technique for changing a cell value as found on Chandoo.org. I have combined this with a Microsoft Pivot Table example that filters a Pivot Table based upon the values in cell D2 (attached example).

The code that powers the hyperlink is:

Public Function highlightSeries(seriesName As
Range)
    Range("RegionFilterRange") = seriesName.Value
End Function
As you hover over C18, D18, E18, it will update the text in D2. This does not trigger a change in the Pivot Table. If you type text in D2 then the Pivot Table updates.

Private Sub Workbook_SheetChange does not seem triggered by the update by highlighSeries.

I've been going round in circles with different methods and can't get it work. Any ideas?

Thanks

Hi,
I have a pivot table showing suppliers broken into categories and then showing total payments and reciepts from them per year.
I have added a calculated field to subtract reciepts from payments to give me a balance of the account.
The calclulated item automatically includes data that has a zero or blank value.
This is giving me loads of blank data i don't want or need.
Does anyone have any suggestions as to how I can filter this?
The filter option isn't ideal as it filters all rows that have a blank in them reagrdless of the column when all I need is for it to filter the rows that have a zero in every column.

thanks

I created a Pivot Table with multiple selections from a Report Filter. My question is once I make a selection is there any way to name the selection so I can retrieve the same selections from the Report Filter without having to remember and make the same selections each time? Any help would be greatly appreciated. Thank you. Jim

Good morning all,

Firstly - first time post but I have been lurking here for years. The ozgrid forums have solved so, so many of my problems over the years and have really taken me from knowing nothing about VBA to being semi-competent. Thanks to all users, admins and anybody else involved.

Ok, to my problem.

Goal - to obtain the "Top 20" from a Pivot table, sourced from multiple worksheets (2011 data, 2012 data).

Problem - I cannot manipulate the source data in each worksheet, and a Pivot Table with multiple sources seems to automatically define the row label as the left-most value in my individual worksheets. The data I wish to have as a row label is in fact contained in column G of each worksheet.

Essentially I am looking to return the Top 20 clients by revenue earned - with client name in column G, and revenue earned in column P. Column A, currently being used as the row labels, contains "2011" and "2012" respectively.

What would be some other options to emulate this feature?

I have considered temporarily combining both worksheets, creating a pivot on the combined data, extracting the top 20, then deleting the new worksheet & pivot. It will likely work....but seems inelegant and cumbersome.

Also possibly relevant to that point - 2011 data contains headers then data in rows 1 & 2. 2012 data contains a criteria table in rows 1-3 with the matching headers/data commencing rows 4 & 5.

I have also considered using VBA to re-order the columns so that the client name column moves to column A, but again this seems like a lot of work and I figure there is an easier solution.

I also considered array formulas but wrote them off due to the sheer number of unique client names.

Any advice or direction would be greatly appreciated.

Thanks all,

Mike.

Hello,

I'm attempting to compare summary values contained within a pivot table with those held on a worksheet.

Briefly,

the pivot table will have x number of summary fields each containing a value. These values are to be compared with those held elsewhere. I believe I need to use the GETPIVOTDATA function, but that's as far as I've got!

many thanks

dom

Hi--

I am trying to create a pivot table with data filtered by 'unique entry', but when I highlight the data and create the pivot table, it uses all of the rows, even those that are repeated (i.e. not unique).

How can I make a pivot table and only use the unique entries? Any help would be much appreciated. Thanks!

I am trying to create a pivot table with multiple data fields but I want these data fields to be their own column. Currently every additional data field I add nests under the first one in the list.

Currently I see it like this...
Customer1Sum of Charges100Sum of Value1000Customer2Sum of Charges200Sum of Value2000Customer3Sum of Charges300Sum of Value3000Customer4Sum of Charges400Sum of Value4000Customer5Sum of Charges500Sum of Value5000

But I really want to see it like this...
Sum of ChargesSum of ValueCustomer11001000Customer22002000Customer33003000Customer44004000Customer55005000

Please help!
Thanks

I'm trying to get a pivot table working with multiple ranges. Basically, one range has a list of people's names (many of which repeat often). The second range has a list of how much they've spent with us. I'd like to break down the list by how many times their name came up (one easy range), and by how much each of them spent with us. I can get the first, but the second one is giving me nightmares. Any suggestions?

I am having trouble using a calculated item with a pivot table that has multiple row fields. The table below shows correctly.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNO3Sum of Amt Month            4CustomerLocationJanFebMarAprMayJunJulAugSepOctNovDecGrand Total5Customer ALocation 1100010001000100010001000100010001000100010001000120006 Location 2100010001000100010001000100010001000100010001000120007 Location 3100010001000100010001000100010001000100010001000120008Customer A Total 300030003000300030003000300030003000300030003000360009Customer BLocation 11000100010001000100010001000100010001000100010001200010 Location 41000100010001000100010001000100010001000100010001200011 Location 51000100010001000100010001000100010001000100010001200012Customer B Total 3000300030003000300030003000300030003000300030003600013Customer CLocation 51000100010001000100010001000100010001000100010001200014 Location 61000100010001000100010001000100010001000100010001200015 Location 71000100010001000100010001000100010001000100010001200016Customer C Total 3000300030003000300030003000300030003000300030003600017Grand Total 900090009000900090009000900090009000900090009000108000Sheet2 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

However, when I add a calculated field for Q1 (using field "Month" =Jan+Feb+Mar) every location is displayed for each customer. Like this:

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNOP3Sum of Amt Month   

I am making a button that will print multiple pivot tables with a single click. But the tables will update periodically. So I am looking for a way to 'name' the ranges to which the pivot tables belong in the macro, then print the named range, rinse and repeat.

I think I can do most of it by simply recording the macro... but, Can someone help me in finding out how to set a range to reference a pivot table as a whole, rather than manually select the range to which the pivot table belongs each time?

Hi -

How do I make a formula in a pivot table based on the value of a column?
I have a pivot table with the results of totals by employee number of 4
types of loans, type 1, type 2, type 3 and type 4. I get the count and
amount of the loans by employee. I want to add type 1 to type 3 for each
employee and get a total count and total amount. I am stuck.

Example Data

Employee
Number Type Amount
457 Type 1 $578,459.00
922 Type 1 $4,578,884.00
922 Type 3 $588,900.50
311 Type 4 $657,844.00
457 Type 2 $9,587.00
457 Type 3 $654,789.00
311 Type 1 $159,753.00
922 Type 2 $258,456.00
311 Type 3 $951,753.00
457 Type 4 $987,357.00
311 Type 1 $111,111.00
311 Type 1 $544,852.00
311 Type 3 $54,897.00
922 Type 4 $95,187.00
922 Type 2 $175,844.00
311 Type 3 $247,544.00
457 Type 1 $9,875.00
311 Type 2 $10,079.00
922 Type 3 $600,457.00
311 Type 4 $578,155.00

I get a pivot similar to this

Emp # Type 1 Type 2 Type 3
Type 4
Count $ Count $ Count $
Count $
311 3 815716 1 10079 3 1254194 2 1235999
457 2 588334 1 9587 1 654789 1 987357
922 1 4578884 2 434300 2 1189357.5 1 95187

How do I use the calculated field to add Type 1 and 3 together?

Thanks so much!

Hi,

Is there a way to create a pivot table which has rows/column selection
values which are not included in the base data?
I have dynamic base data, each row consisting of ProjectName , ProcedureName
and %compliance. The ProjectsNames are selected from a dropdown list of 10
possible projects.

I have created a pivotTable from the base data, with projectName on the
y-axis and ProcedureName on the x-axis, and %compliance in the middle.

If I only have rows of base data with say 5 of the possible 10 ProjectNames,
on the pivotTable i only then have the option of choosing between these 5
projectNames to display. Is it possible to include all 10 of the ProjectNames
in the selection in the pivotTable? Even when they are not all in the base
data? so if I picked one without any base data it would just show blank
%compliance info?

thanks very much for any help

cheers
Barbara