Pivot table Options

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

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


"wal50" <wal50@discussions.microsoft.com> wrote in message
news:604DB6EE-E7E3-407E-8D04-98943FC03AE5@microsoft.com...
> 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

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.

"Bernie Deitrick" wrote:

> 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
>
>
> "wal50" <wal50@discussions.microsoft.com> wrote in message
> news:604DB6EE-E7E3-407E-8D04-98943FC03AE5@microsoft.com...
> > 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
>
>
>

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" <wal50@discussions.microsoft.com> wrote in message
news:B50E5949-80E7-4D5C-AD4B-C725052D7F6A@microsoft.com...
> 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.
>
> "Bernie Deitrick" wrote:
>
> > 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
> >
> >
> > "wal50" <wal50@discussions.microsoft.com> wrote in message
> > news:604DB6EE-E7E3-407E-8D04-98943FC03AE5@microsoft.com...
> > > 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
> >
> >
> >

Thanks much.
wal50

"Bernie Deitrick" wrote:

> 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" <wal50@discussions.microsoft.com> wrote in message
> news:B50E5949-80E7-4D5C-AD4B-C725052D7F6A@microsoft.com...
> > 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.
> >
> > "Bernie Deitrick" wrote:
> >
> > > 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
> > >
> > >
> > > "wal50" <wal50@discussions.microsoft.com> wrote in message
> > > news:604DB6EE-E7E3-407E-8D04-98943FC03AE5@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>