COUNT Unique values

[GregS]

Hello,

Is there a way to COUNT Unique values and showing only
those values where the Count is > 1 ....Please Note: The
Count must show up on a seperate WorkSheet ( Audit
WorkSheet) not on the Source WorkSheet which will be
imported into Access ........ this, I beleive, makes
Filter Unique not an option.

Thanks,

Greg

 

[Frank Kabel]

Hi Greg
I think you asked this question before
What do you mean with Count > 1?
Please provide an example of your data (plain text - no attachment
please)

 

[Greg]

Hello Frank,

I am looking for duplicate values in a column which will
be imported as a key field in Access ...... i.e. the
following example has one duplicate: 123 .....

EmpNo
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
123

Thanks,

Greg

 

[JWolf]

See:
http://support.microsoft.com/defaul...port/kb/articles/Q268/0/01.ASP&NoWebContent=1

 

[Greg]

Thanks,

I've tried this earlier and it did not work !!!

again, Thanks for responding

Greg

 

[LarryP]

Hi, Greg. This isn't exactly what you're asking for, but
it's a useful little duplicate-finder macro that I put in
my Personal.xls because I use it all the time. Starting
at the top of any continuous column of data, it inserts a
new column and fills it with either "UNIQUE" (for the
first occurrence) or "DUPLICATE" (for second and
subsequent occurrences). At the end it gives a message
box showing items evaluated and count of unique items.
Maybe you can massage it into something that'll solve your
problem.
########################################################
Sub Duplicates()
Dim strValArray() As String
Dim lngCounter As Long

Selection.EntireColumn.Insert
ReDim strValArray(0)
strValArray(0) = ActiveCell.Offset(0, 1).Value
ActiveCell.Value = "Unique"
lngCounter = 1
Do While ActiveCell.Offset(1, 1).Value > ""
ActiveCell.Offset(1, 0).Select
'FindDups
Dim x As Long
x = 0
Do Until ActiveCell.Value = "Duplicate" Or x =
UBound(strValArray) + 1
If strValArray(x) = ActiveCell.Offset(0,
1).Value Then
ActiveCell.Value = "Duplicate"
Else
x = x + 1
End If
Loop


If ActiveCell.Value <> "Duplicate" Then
ReDim Preserve strValArray(UBound(strValArray)
+ 1)
strValArray(UBound(strValArray)) =
ActiveCell.Offset(0, 1).Value
ActiveCell.Value = "Unique"
End If
lngCounter = lngCounter + 1
Loop
MsgBox ("Items checked = " & lngCounter & ", Unique
Count = " &
UBound(strValArray) + 1)
End Sub
######################################################

 

[Frank Kabel]

Hi
in B2 enter the following formula
=IF(COUNTIF($A$1:$A$1000,A1)>1,"X","")
copy this down and filter with this column