Identifying unique values in range

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

Jim

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
 
Sounds like a mission for the advanced filter, select the list/table,
do data>filter>advanced filter, select copy to another location (put in the
first cell where
you want the start of the list), check unique records only and click OK
 
Select your column. Choose Data/Filter/Advanced Filter. If you get a
warning that XL can't determine a header, click OK. Select the Copy
list to another location radio button and check the Unique records
only checkbox. Leave the criteria range blank and put your desired
location in the location textbox. Click OK.
 
Data -- Filter -- Advanced Filter. Copy to another location (specify where
in "Copy to"), check "Unique records only", click OK.

Rgds,
Andy
 
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

'===================
 
Back
Top