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" <(E-Mail Removed)> wrote in message
news:B50E5949-80E7-4D5C-AD4B-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:604DB6EE-E7E3-407E-8D04-(E-Mail Removed)...
> > > 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
> >
> >
> >
Similar Threads
