Identyfying unique values in a range

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Thanks for the Help! Here is my orig question:
I have a column of data with many duplicate results (see
column on left). In a separate column or range I want to
automatically show just the unique results (see column on
Right). PLEASE...how do I do this For example:

Unit Type Unique
Studio A Studio A
Studio A Studio C
Studio C
Studio C
Studio C
Studio A
Studio C

Here is my follow-up. Using advanced filter does not
allow the unique column to automatically update--its just
hard coded into the column. How can I get this to update
automatically? There must be a way!!! Thanks so much!
 
Your previous post of less than 1 hour ago has already been replied to.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Pushed the button before I finished.

.... and so it is best to keep to the same thread.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
This is a follow up question. I know how to use advanced
filter but it does not provide automatic updating.
 
As answered in your previous thread:

To do this automatically, you can use a Worksheet Change event, that
runs an Advanced Filter. Right-click on the sheet tab, and choose View
Code. Paste the following code into the code module, where the cursor is
flashing.

When you add an item to Column A, the list in Column C will be updated

'=========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & r).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), Unique:=True
End If
End Sub

'===================
 
Whoa you are good! I did the past but it did not work
because I am an idiot I suspect. I am filter from E5:e254
and sending them to a range starting at E266.

Not sure if that is helpful but I have not done visual
basis before...but I am going to learn it!
 
With that set range, you could use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E5:E254")) Is Nothing Then
Exit Sub
Else
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Range("E5:E255").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("E266"), Unique:=True
End If
End Sub
 
Correction -- in the Advanced Filter line, the range should be:
Range("E5:E254").AdvancedFilter

Whatever is in cell E5 will be treated as a heading, so start your list
of items in E6, or adjust the code to include your heading cell.
 
Back
Top