I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data
a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears
in this case number of uniques items is 3
Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this. Thanks for any comments.
Last edited by maacmaac; 10-10-2008 at 02:18 PM.
Does this help ? =SUM(1/COUNTIF(a2:a11,a2:a11)) entered as an arry formula ( Ctrl+Shift+Enter)
Try...
=SUM(IF(FREQUENCY(IF(A1:A7<>"",MATCH("~"&A1:A7,A1:A7&"",0)),ROW(A1:A7)-ROW(A1)+1),1))
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
Domenic -
Your solution works if I have 7 rows of data but the total number of rows varies day-to-day (usually 5000 rows to 30000 rows). I tried to adjust the formula as follows but does not work.
=SUM(IF(FREQUENCY(IF(A:A<>"",MATCH("~"&A:A,A:A&"",0)),ROW(A:A)-ROW(A1)+1),1))
Unfortunately, array formulas such as this one do not allow whole column references. If you're using Excel 2007, convert your data into a Table. The range will automatically adjust as data is added/removed. If you're using Excel 2003, convert your data into a List. Otherwise, use a dynamic named range.
Hope this helps!
Maybe using dynamic ranges will do the trick?
I think I can figure it out from here (I hope). Thanks for the link for dynamic ranges.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks