Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Creating pivot table with filtered data

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!


Post your answer or comment

comments powered by Disqus
Experts,

I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)

Can anyone help me to resolve this issue?

Thanks in advance!
Venkat

Hello
I have managed to create a pivot table to filter data that shares a common relationship (in this case it is data from fish that are in their early rearing stage of life). But what I need to do is display the raw data for each fish in their own worksheet. I seem to recall I learned to do this months ago but I have forgotten and cannot find anything on the internet Is there a keyboard shortcut to do this?
Thanks!
Lora

Hi there,
I am writing a macro to generate a pivot table from some data. I want it to select all the data in a particular sheet "Wk_Data", and create the pivot in a separate sheet called "InterimHMPivot". In the below code, I try to set a variable AllData as the range of all the data in Wk_Data, and generate the pivot, however it gives me an error saying type mismatch on the "ActiveWorkbook.PivotCaches.Create" code. If i replace AllData with a manually coded range (as I have below with "Wk_Data!R1C1:R79135C120") then the code all works. If I put a breakpoint and check what AllData is, then it returns $A$1:$DP$79135 (the correct range.). I'm guessing it has to do with the AllData range not specifying the worksheet since I change sheets before creating the pivot?

Do you have any pointers as to what I am doing wrong?

Thanks
Ben

	VB:
	
 ' Select all raw data
Sheets("Wk_Data").Activate 
Range("A1").Select 
Range(Selection, Selection.End(xlToRight)).Select 
Range(Selection, Selection.End(xlDown)).Select 
Dim AllData As Range 
Set AllData = Selection 
 
 'Clear existing data in the sheet
Sheets("InterimHMPivot").Select 
Cells.Select 
Selection.Clear 
 
 'Create the pivot table
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
"Wk_Data!R1C1:R79135C120", Version:=xlPivotTableVersion14).CreatePivotTable _ 
TableDestination:="InterimHMPivot!R3C1", TableName:="PT_InterimHMPivot", DefaultVersion _ 
:=xlPivotTableVersion14 

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


Hi all,

I would like to create a pivot table using data from a txt file. I don't want to import the data into excel first, then make a pivot due to the size of the data.

I've done something similar before by using an excel file on a server to create the pivot table but I'm unable to figure out how to do this with a txt file. In the past I've used create pivot table and then chose external data source.

Is this possible, with or without VB? TIA!

Dynamically Sourced Range Pivot Tables with Excel VBA.
A common problem with automating pivot tables is how to make the VBA generic so that when the number of rows or columns changes that the code sitll works. In this example it shows how to make a pivot table no matter how many rows or colums there are and also how to place this pivot table in a specific destination. Enjoy.

download sample book
http://programminglibrary.com/Progra...ta sources.xls


	VB:
	
 CreatPivot() 
    Call AutoPivot("Data", "Name", "Animal Type") 
End Sub 
Sub AutoPivot(strPivsheet As String, strPivCol1 As String, strPivCol2 As String) 
     'USE-EXCEL VBA CREATE SIMPLE PIVOT TABLE FROM COLUMNIZED DATA REGARDLESS OF NUMBER OF ROWS OR COLUMNS USING DYNAMIC RC
NOTATION
     'CREATED BY MARK SLOBODA
     'DOWNLOAD COOL EXCEL STUFF :http://programminglibrary.com/Programming%20Library/DOWNLOAD/downloads.aspx
     
     '************NOTES*****************************
     '1)  ASSUMES DATA STARTS IN 1,1
     '2)  IN THIS EXAMPLE YOU ARE USING VARIABLES TO MAKE DATA SOURCE SIZE OF NUMBER OF ROWS AND COLUMNS AND SHEET REGARDLESS
OF SIZE
     '3)  YOU COULD PASS AN ARRAY OF PIVOT COLUMN FIELD NAMES OR SIMPLY ADD OTHERS
     
     '**************** VARS PASSED *****************
     'strPivsheet - SHEET WHICH PIVOT TABLE WILL BE CREATED FROM
     'strPivCol1 - PivotColumn1 (Used for Row and Data Fields)
     'strPivCol2 - PivotColumn2 (Used for Column Field)
     
     'DEC VARS
    Dim lngR As Long '#ROWS-ALWAYS USE LONG TO AVOID MAX INTEGER ERROR
    Dim intC As Integer '#COLUMNS
     
     'SET VALS
    Sheets(strPivsheet).Select 'SELECT SHEET TO CREATE PIVOT TABLE ON
    Range("A1").Select 'SELECT A CELL IN ACTIVE DATA RANGE
    lngR = Range("A1").CurrentRegion.Rows.Count 'GET ROW COUNT ON ACTIVE SHEET
    intC = Range("A1").CurrentRegion.Columns.Count 'GET COLUMN COUNT ON ACTIVE SHEET
     
     'BEGIN CODE
     'CREATE THE PIVOT 2 VERSIONS
     
     '******************* VERSION 1 NO SPECIFIC DESTINATION SHEET ********************************
     '********************************************************************************************
     
     'CREATE PIVOT
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ 
    "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="", TableName:="PivotTable1" 
     
     'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED VARIABLE
USED FOR PIVOT COLUMN
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields(strPivCol1), "Count of" & strPivCol1, xlCount 
     
     'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1) 
        .Orientation = xlRowField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2) 
        .Orientation = xlColumnField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     
     '******************* VERSION 2 SPECIFIC DESTINATION SHEET ********************************
     '*****************************************************************************************
     
     '    Sheets.Add    'CREATE A NEW SHEET OR ASSIGN EXISTING ONE TO VARIABLE
     '    strDynamicSheet = ActiveSheet.Name    'ASSIGN DYNAMIC SHEET
     '    Sheets(strPivsheet).Select    'GO BACK TO PIVOT DATA SHEET
     '
     '    'CREAT PIVOT
     '    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
     '                                 "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="'" &
strDynamicSheet & "'!R1C1", TableName:="PivotTable1"
     '
     '    'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED
VARIABLE USED FOR PIVOT COLUMN
     '    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
     '                                                        "PivotTable1").PivotFields(strPivCol1), "Count of" &
strPivCol1, xlCount
     '
     '    'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1)
     '        .Orientation = xlRowField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     '
     '    'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2)
     '        .Orientation = xlColumnField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     
End Sub 

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


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 !!!

Data Source 'A': Customer monthly sales
Data fields: Cust_name, Apr 05, May 05, Jun 05, Jul 05

Data Source 'B': Customer Info
Data fields: Cust_name, Cust_ID, Cust_Group_ID

[Cust_ID is unique but Cust_Group_ID is not unique]

Task is to create a Pivot Table. Only Cust_Group_ID, Apr 05, May 05, Jun 05, Jul 05 are the data needed in the Table.

Theres more requirement, only those Cust_Group_ID = 100 200 and 300 can be shown in the table.

I know its easilier if the Customer monthly sales contains Cust_Group_ID field. How can I select certain Data fields from different Data source and merge it together?

P.S. I have been thinking, do I have to Merge the required Data field from different Data source together to create a new data source first. than create the Pivot Table.

P.S. I tried select "Multiple consolidation ranges" while creating Pivot table but it just merged the selected field from 2 data source and still cannot sort those sales data of Cust_Group_ID = 100 200 and 300. please help

Can anyone help me please.

I have a pivot table that I formatted etc and now want to update the source data. I dont want to create a new pivot on the new data but use the existing pivot table with the new data.

Is there a way I can tell the pivot table which data to now look at ?

Many thanks for any help.

Mark

I want to create a pivot table with a macro (using record macro and then create a pivot table) along with a button to run next time for different data. but after stoping when I am trying to run the macro I am getting this error
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Sheet2 (2)'!C1:C2").CreatePivotTable TableDestination:= _
        "'[GE.xls]Sheet2 (2)'!R1C4", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
If some one can help me in this regard would be appreciated.
I want to clear that I don't know much about VB. Pls. Help

Rana

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.

I have data in which I create 3 pivot tables in a new tab. I have to manually do this for about 15 files per month. Is there any way I can create a macro to create these pivot tables with the click of a button?

If so I'll post a small example of what it looks like complete. I have recorded macros for this step multiple times and can't seem to get it. I always get errors because the files names are different. Also when recording macros with pivot tables it records the Sheet1, Sheet2, and so on, I don't know how to make that vague. Any help would be great, I would really appreciate it.

Hi....I dont know if I am the right path....


	VB:
	
 
 'Initiate PivotCache object to accept external data
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 
 
 'Assign the recordset to PivotCache object
Set objPivotCache.Recordset = rs 
 
 'Create pivot table
With objPivotCache 
    .CreatePivotTable TableDestination:=ws.Range("Z10") 
     'Use this line of code instead if you want to specify a tablename for the pivot table
     '.CreatePivotTable TableDestination:=ws.Range("Z10"), TableName:="ABCDEFG"
End With 
 
 'Place the pivot field items in table
 'Place the field item "Week" into the column section
 
[COLOR=red]With objPivotCache.PivotTables

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.

I recorded the creation of a pivot table with the macro recorder.
When I check the VBA script and try to repeat the same action on the same file again the macro reports an error.
Unfortunately I use the Dutch version of excel 2007 so I'll translate the error description: "Run-time Error 5 - Invalid Procedure Call or Argument"

Below the VBA code in the VBA editor:
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Blad1!R1K1:R3112K9", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Blad8!R3K1", TableName:="Draaitabel3", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Blad8").Select
Cells(3, 1).Select

I had several problems when i changed to excel 2007, but this one I can't tacle.
The problem occurs on different environments: Within citrix and excel 2007, winthin win XP + excel 2007 and win Vista + excel 2007.

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

I am trying to create a Pivot Table that uses data that is in a different sized Range every day. For example the range may be 1000 rows long and 25 columns wide one day and then be 1100 rows long and 26 columns the next day. I want the pivot table to update automatically without me having to amnually redo the Pivot Table every day. I know that there is a way to do this and it involves using the functions "counta" and "offset" but I have not been able to piece them together. Any suggestions.

Thanks

Is this possible? I'm starting with a pivot table with 2 data fields (Sales
and %Change). I have the data sorted by Sales, which is what I want. I
don't, however, want the Sales field to be displayed. When I hide the Sales
field, my sorting goes away. After further experimentation, I've found that
hiding a data field is nothing more than removing it from the pivot table.
Is there a way that I can only display %Change, but have my data sorted by
Sales?

Thanks!

Jeff

Hi,
Please help me in Synchronizing two Pivot tables of same data with One Report Filter.

I have almost done with the Following VB Code found in the Web, but as i dont know how to add some more fields in the Code.(Ex: The below code only changes my "Region" filter but i have some more filter field in my pivot table as "State", "Town".) If you would have tell me how to add more strings(Report Filter Field) in the following code will also help me in closing this.

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Region"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Hi

I have data with more than 100,000 line items and I want to create a Pivot
Table out of it. The data would not fit in one sheet.

How do I create one Pivot table with data in more than one sheets.

Thanks..
Rehan

Using Excel 2002 on an XP system, I have developed Pivot Tables on three different worksheets, with the data on the mainworksheet of the Workbook. I am having trouble updating the records and getting the Pivot Table to update. I am pasting the data from another seperate workbook into the same location as teh old data. And then selecting the range of data and resetting the Name. I have then tried to use the Pivot Table Wizard to update the range but get problems in the existing Pivot Tables and a new blank Pivot Table is created. Is there a better way?

Hi everybody,
I'm a beginner and i'm trying to record a macro that would create a pivot table with customer as row and count of customer as the data field...
the code created is given below, but i get error if i run the macro, pls help to locate the error...

	VB:
	
 piv() 
     '
     ' piv Macro
     ' Macro recorded 3/8/2007 by audit
     '
     
     '
    Range("A1:K9").Select 
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    "workings!R1C1:R9C11").CreatePivotTable TableDestination:="", TableName:= _ 
    "PivotTable2", DefaultVersion:=xlPivotTableVersion10 
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    ActiveSheet.Cells(3, 1).Select 
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=" Customer :" 
    ActiveSheet.PivotTables("PivotTable2").PivotFields(" Customer :").Orientation _ 
    = xlDataField 
    ActiveWorkbook.ShowPivotTableFieldList = True 
End Sub 

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

Hi -

I'm very new at macro, vba, and pivot table. So please bear with me while I try my best to explain what I need help on.

My challenge:

I receive a spreadsheet that shows how takes what training and when. It also includes the region in which the user resides in. With this data, I need to create a summary report. Below is a sample of the spreadsheet.

User ID (UID)RegionType of TrainingJan 6 2008Jan 7 2008Jan 8 2008Jan 9 2008Jan 10 2008afzalazEUROPEABC1 afzalazEUROPEABC2 afzalazEUROPEABC1
greenmelAMERICASDEF1 greenmelAMERICASDEF1 greenmelAMERICASDEF1
The requirement for my report generation must show what types of training each region is using, and the pivot table looks like it's the best way to get me that data. Below is the pivot table sample.

RegionDataAMERICASEUROPENJAGrand TotalCount of Jan 6 200811Count of Jan 7 200822Count of Jan 8 200821012Count of Jan 9 200877Count of Jan 10 200811

OK. Now the problem:

I need to find a way to create this pivot table, but as you can see, the date will change each week. The spreadsheet I get is also inconsistent because sometimes it may have 3 days or 6 days of data on it. How can I create a macro to run a pivot table when my data fields is not constant?

If you need further clarification, I am happy to do so. Any help is great appreciated.

Thanks so much!

Hi All

I am trying to create a PT from a selection of data inported from a closed worksheet I have managed to inport the data with some help from Norie, but now my code to create the PT is not working.

Code:
Sub CreatePivot()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("PivotTable")

    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTable
        PT.TableRange2.Clear
    Next PT

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row & column fields
    PT.AddFields RowFields:="Item"

    ' Set up the data fields
    With PT.PivotFields("ON_HAND")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .NullString = "0"
    End With


    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

End Sub
When I run the above code it returns a compile error, Method or data member not found. At this piece of code

Code:
' Delete any prior pivot tables
    For Each PT In WSD.PivotTable
The worksheet that contains my data is called PivotTable and this is where I would like the pivot table to also be created.

Thanks Danny

I have a somewhat large file that I'm trying to dynamically subtotal
with a pivot table and retreive data via vba and put it in another
workbook (2 open at once). There are two questions I have: 1) Is the
method I'm using the most efficient or is there a better way?, and 2)
If my method is okay, then what am I doing wrong?

Okay, the details... The header has depts (potentially from 1-100 in
the columns), season-type (1-50), metric type (sales, inventory,
receipts, etc) then 26 weeks going across the top. I need to
dynamically sum differing combinations of departments by season-type
for a given metric by week. (Not all possible depts or season types
will always exist.) I've created a pivot table with vba and then can
easily update the pivot table with the combinations that I want to see,
but my problem becomes when I'm trying to use the .getdata command. If
a given combination doesn't exist then run-time error 1004 pops up.
I've put in an error trap and it catches some but not all (it gets
through about 7 of them????!!!!).

'dept is in the page field of the pivot table summing depts 1-25
lkup = "7/23/2005"
ssn = 11 (a variable, but 11 here for example)
metric = "Sales" (again, another variable)
div_ssn = "'Sum of " & lkup & "' " & ssn & " " & metric
Workbooks(datawb).Activate
On Error GoTo chk_error
act_sls_data = ActiveSheet.PivotTables("data_pivot") _
.GetData(div_ssn)
chk_error:
Select Case Err.Number
Case Is = 1004
act_data = 0
End Select

ThisWorkbook.Activate
range("sales1").Cells(ssn_cntr, curr_col).Offset(0, y) =
act_sls_data

Any ideas? How about Consolidate or Group or Subtotal with this amount
of data? Needs to be fast and dynamic.

Thanks!
Pete


No luck finding an answer? You could always try Google.