Filtering

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Hi, can ayone tell me how I could do the following in excel 2007 or
2010:



Horse Trainer

Sam M Jones
John M Jones
Colin S Attwater
Steve J Jackson
Brian B Mathers
James B Mathers
Martin G Raggy
Patrick S Thomson


What I want to do is filter the dataset on 'Trainer' so that it only
shows me the Trainers who feature once in the list. So if I was to do
this properly it would only show S Attwater and G Raggy as the other
trainers have more than one horse.....


Hopefully someone can help because it is driving me crazy !!

Thanks

David
 
Give this macro a try...

Sub HideDuplicateTrainers()
Dim X As Long, R As Range, LastRow As Long, LastColumnPlusOne As Long
Const TrainerColumn As String = "B"
Const DataStartCell As Long = 2
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Rows.Hidden = False
LastRow = Cells(Rows.Count, TrainerColumn).End(xlUp).Row
LastColumnPlusOne = Cells.Find(What:="*", SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Range(Cells(DataStartCell, TrainerColumn), Cells(LastRow,
TrainerColumn)).Copy Cells(DataStartCell, LastColumnPlusOne)
For X = DataStartCell To LastRow
If WorksheetFunction.CountIf(Range(Cells(DataStartCell,
LastColumnPlusOne), Cells(LastRow, LastColumnPlusOne)), Cells(X,
LastColumnPlusOne).Value) > 1 Then
With Range(Cells(DataStartCell, LastColumnPlusOne), Cells(LastRow,
LastColumnPlusOne))
.Replace Cells(X, LastColumnPlusOne), "", xlWhole, , False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
End If
Next
Application.ScreenUpdating = True
Columns(LastColumnPlusOne).Clear
End Sub

Rick Rothstein (MVP - Excel)






wrote in message
Hi, can ayone tell me how I could do the following in excel 2007 or
2010:



Horse Trainer

Sam M Jones
John M Jones
Colin S Attwater
Steve J Jackson
Brian B Mathers
James B Mathers
Martin G Raggy
Patrick S Thomson


What I want to do is filter the dataset on 'Trainer' so that it only
shows me the Trainers who feature once in the list. So if I was to do
this properly it would only show S Attwater and G Raggy as the other
trainers have more than one horse.....


Hopefully someone can help because it is driving me crazy !!

Thanks

David
 
(e-mail address removed) was thinking very hard :
Hi, can ayone tell me how I could do the following in excel 2007 or
2010:



Horse Trainer

Sam M Jones
John M Jones
Colin S Attwater
Steve J Jackson
Brian B Mathers
James B Mathers
Martin G Raggy
Patrick S Thomson


What I want to do is filter the dataset on 'Trainer' so that it only
shows me the Trainers who feature once in the list. So if I was to do
this properly it would only show S Attwater and G Raggy as the other
trainers have more than one horse.....


Hopefully someone can help because it is driving me crazy !!

Thanks

David
Click to expand...

I have used a formula to enter a 'flag' in a program column that I can
autofilter on.

Example:
ColB is named "TrainerNames" and defined as fully absolute ("$B:$B").
Cell B1 is active and named "TrainerName", and is defined as
column-absolute, row-relative ("$B1")

In the program column:
Select row1 and enter:
"=IF(COUNTIF(TrainerNames,TrainerName)=1,"TRUE","FALSE")
This sets the value to "TRUE" if the trainer's name appears only once.
I can filter on this to hide all other names.
 
Back
Top