Unique Records
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 |
Use datafilteradvanced 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 "Arturo" wrote: 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 |
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Peo Sjoblom" wrote in message ... Use datafilteradvanced 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 "Arturo" wrote: 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 |
All times are GMT +1. The time now is 06:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com