Unique Records

[Arturo]

If I have a list of numbers
1
2
3
2
1
2

How would I generate a list unique records?
I.e.
2 of 1
3 of 2
1 of 3

 

[Peo Sjoblom]

Use data>filter>advanced filter, copy to another location and unique records
only, if you want to count the list use countif on the whole list with the
unique list as criteria

Whole list is A2:A400, unique list is H2:H40, in I2 put

=COUNTIF($A$2:$A$400,H2)

copy down as long as needed

Regards,

Peo Sjoblom

 

[Ron de Bruin]

Hi Arturo

I like to add this to Peo's reply

You can also use a Pivot table
http://www.contextures.com/tiptech.html


Or with a macro this
With your data in Column A, it copy a Unique list in C
and count the Unique items in D

Sub Test()
UniqueList
CountUniqueItems
End Sub


Sub UniqueList()
'Cell A1 is a header
With Sheets("sheet1")
.Range("A1", .Cells(Rows.Count, "A").End(xlUp)) _
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("C1"), _
CriteriaRange:="", Unique:=True
End With
End Sub

Sub CountUniqueItems()
Dim cell As Range
With Sheets("sheet1")
For Each cell In .Range("C2", .Cells(Rows.Count, "C").End(xlUp))
cell.Offset(0, 1) = Application.WorksheetFunction.CountIf _
(.Range("A2", .Cells(Rows.Count, "A").End(xlUp)), cell.Value)
Next
End With
End Sub

Sub Test()
UniqueList
CountUniqueItems
End Sub