Counting groups of exact case numbers w/letters in them.

[Guest]

I need to know how I can write a formula to count a very big list of numbers
and return the total number of numbers that are different in the list. For
example:
CAH8800719
CAH8800718
CAH8800718
CAH8800717
FJG8800719
FJG8800715
CAH8800719
FJG8800715
As you can see there are 8 case numbers in this list but of those 8 there are
only 5 different case numbers. So if I had a list of several hundred or
thousand case numbers like the ones above, and many of them had duplicates
somewhere in the list, how can I get excel to give me a total number of all
the similar case numbers?
I can use the following formula if the case numbers are only numeral:
=SUM(IF(FREQUENCY(A:A,A:A)>0,1))
but this only works if the case numbers are strictly numerical, how can I
accomplish the same thing if some case numbers are numerical and some include
letters? plz help.

 

[Guest]

Try:
=SUM(IF(FREQUENCY(MATCH(B1:B10,B1:B10,0),MATCH(B1:B10,B1:B10,0))>0,1))

tj

 

[Guest]

Thanks again for you exellent help. The only problem I have with this
formula is that it only works when there are case numbers exactly in the
range given. I need to use the range of an entire column, top to bottom, so
that as case numbers (numerical or alpha-numerical) get added to the list the
formula will count the different case numbers (ongoing) and not count the
duplicate ones. If this makes sense. Dude, you're awesome, or are you
female? anyway, can this be done?

 

[Domenic]

=SUM(IF(FREQUENCY(IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)),IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)))>0,1,0))

...confirmed with CONTORL+SHIFT+ENTER.

Hope this helps!

 

[Domenic]

=SUM(IF(FREQUENCY(IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)),IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)))>0,1,0))

...confirmed with CONTORL+SHIFT+ENTER.

Hope this helps!

Or...

=SUM(IF(A1:A1000<>"",1/COUNTIF(A1:A1000,A1:A1000)))

...confirmed with CONTROL+SHIFT+ENTER

 

[Guest]

doesn't seem to be working.

=SUM(IF(FREQUENCY(IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)),IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)))>0,1,0))

...confirmed with CONTORL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Do you need the entire Column--from A1 all the way to A65536? Or, do you just
need the formula to always account for the all the entries in Column A?
I might be misremebering, but I don't believe that MATCH can accept an
entire Column as an argument.

If all the Numbers are in a contiguous range (no blanks Rows), then you can
do the following:
1. Click on cell A1 (the first cell in the range)
2. Insert | Name | Define
3. Type MyRange for the Name: (no spaces)
4. Then, in the Refers to line, type this:
=OFFSET(Sheet8!$A$1,0,0,COUNTA(Sheet8!$A:$A))
5. Then, try this formula (a modification of the one Domenic posted
=SUM(IF(FREQUENCY(IF(myrange<>"",MATCH(myrange,myrange,0)),IF(myrange<>"",MATCH(myrange,myrange,0)))>0,1,0))

Make sure you enter it with Ctrl+Shift+Enter.
You may need to copy the formula to a text editor (like Notepad) to make
sure you get it all on one line and don't have any spaces at the end of it.
Then, copy from the text editor to Excel. Double_click on the cell, and then
press Ctrl+Shift+Enter.

If you have blank rows in the data list, this formula may not return the
correct result.


tj

 

[Guest]

Here is a programmatic solution that may work for you. This macro was taken
from:
http://www.exceltip.com/show_tip/Pr..._values_using_VBA_in_Microsoft_Excel/520.html

If you are new to macros, look at before using the code below:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function

Hope this helps,
tj

 

[Domenic]

doesn't seem to be working.

What do you mean it doesn't work? The formula is correct and works
fine. Are you trying to reference the entire column? If so, try the
second formula I offered instead...

=SUM(IF(A1:A65535<>"",1/COUNTIF(A1:A65535,A1:A65535)))

...entered using CONTROL+SHIFT+ENTER.