Pivot table Options

G

Guest

I have a daily file. Column A is a manufacturer (multiple) , column B is
model (multiple), Column C is machine number (unique). Other columns show
types of activity for a total of ~10K records per day. But only about 40
make/model groups with 1000 unique instances.
I would like to use a pivot table to determine how many models of each
machine are in use each day. Is it possible to use a pivot table to count
unique records of each Make/model? The current macro uses Sumproduct to
identify unique records and then counts each make/model combo with sumif. I
am looking for a faster option. Any ideas would be appreciated.
Thanks
 
B

Bernie Deitrick

It would help if you posted a very small example table, to help in your
description of what you mean by "unique records", and also what results you
would expect from the table.

HTH,
Bernie
MS Excel MVP
 
G

Guest

Make1 Model1 Machine1 Other data...
Make1 Model1 Machine1 Other data...
Make1 Model2 Machine3 Other data...
Make1 Model2 Machine3 Other data...
Make1 Model1 Machine3 Other data...
Make2 Model4 Machine4 Other data...

The result would then be
Make1 Model1 =2 (Line 1&5)
Make1 Model2 =1 (Line 3)
Make2 Model4 =1 (line 6).
Line 2 & 4 are - by my definition - duplicates, since they have the same
make/model/machine#)

I think what I want to do is get a pivot table showing Make/Model with a
count of the unique machines for that make/model - not the number of records
for each machine. I just can't see how to do this or if it is possible.
Autofilter doesn't seem to do it either. Hope this makes it more clear.
 
B

Bernie Deitrick

wal50,

You can do it with a pivot table based on a pivot table. Try the code
below: select a single cell in your data table and run the macro.
Assumptions are that your column headings are "Make", "Model", and "Machine"

It should execute very quickly.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!" & ActiveCell.CurrentRegion.Address _
(, , xlR1C1)).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Make")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Model")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Machine"), "Count of Machine", xlCount

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Machine")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 3
End With

Range("C65536").End(xlUp).CurrentRegion.Copy
Range("C65536").End(xlUp)(5, -1).PasteSpecial Paste:=xlPasteValues
Range("C65536").End(xlUp).CurrentRegion.Cells(1).EntireRow.Delete
Range("C65536").End(xlUp).CurrentRegion.SpecialCells(xlCellTypeBlanks) _
.FormulaR1C1 = "=R[-1]C"
Range("C65536").End(xlUp).CurrentRegion.Copy
Range("C65536").End(xlUp).CurrentRegion.PasteSpecial
Paste:=xlPasteValues

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!" &
Range("C65536").End(xlUp).CurrentRegion.Address _
(, , xlR1C1)).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3"

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Make")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Model")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Machine"), "Count of Machine", xlCount
With ActiveSheet.PivotTables("PivotTable3")
.ColumnGrand = False
.RowGrand = False
End With
End Sub
 
G

Guest

Thanks much.
wal50

Bernie Deitrick said:
wal50,

You can do it with a pivot table based on a pivot table. Try the code
below: select a single cell in your data table and run the macro.
Assumptions are that your column headings are "Make", "Model", and "Machine"

It should execute very quickly.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!" & ActiveCell.CurrentRegion.Address _
(, , xlR1C1)).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Make")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Model")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Machine"), "Count of Machine", xlCount

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Machine")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 3
End With

Range("C65536").End(xlUp).CurrentRegion.Copy
Range("C65536").End(xlUp)(5, -1).PasteSpecial Paste:=xlPasteValues
Range("C65536").End(xlUp).CurrentRegion.Cells(1).EntireRow.Delete
Range("C65536").End(xlUp).CurrentRegion.SpecialCells(xlCellTypeBlanks) _
.FormulaR1C1 = "=R[-1]C"
Range("C65536").End(xlUp).CurrentRegion.Copy
Range("C65536").End(xlUp).CurrentRegion.PasteSpecial
Paste:=xlPasteValues

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!" &
Range("C65536").End(xlUp).CurrentRegion.Address _
(, , xlR1C1)).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3"

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Make")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Model")
.Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Machine"), "Count of Machine", xlCount
With ActiveSheet.PivotTables("PivotTable3")
.ColumnGrand = False
.RowGrand = False
End With
End Sub



wal50 said:
Make1 Model1 Machine1 Other data...
Make1 Model1 Machine1 Other data...
Make1 Model2 Machine3 Other data...
Make1 Model2 Machine3 Other data...
Make1 Model1 Machine3 Other data...
Make2 Model4 Machine4 Other data...

The result would then be
Make1 Model1 =2 (Line 1&5)
Make1 Model2 =1 (Line 3)
Make2 Model4 =1 (line 6).
Line 2 & 4 are - by my definition - duplicates, since they have the same
make/model/machine#)

I think what I want to do is get a pivot table showing Make/Model with a
count of the unique machines for that make/model - not the number of records
for each machine. I just can't see how to do this or if it is possible.
Autofilter doesn't seem to do it either. Hope this makes it more clear.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Turning Data into a Table 1
Pivot - show as % of sub-total 7
How to show a count of Unique IDs in a Pivot Table 2
Pivot table report invalid 0
Adding additional column of data to pivot table 1
Sumproduct function allow a lookup for one Date to check if it fallsin Date Range in other table 9
Pivot Table Help! 1
Pivot Table - Max Unique Items 2

Top