MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNT UNIQUE TEXT IN COLUMN


Posted by YANECKC on April 15, 2001 5:01 PM

I WOULD LIKE TO COUNT TEXT IN COLUMN A, BUT ONLY
COUNT UNIQUE TEXT. SEE EXAMPLE BELOW.
COLUMN 1
CAT
DOG
BIRD
FISH
FISH
SNAKE
CAT
THE TOTAL WOULD BE FIVE (CAT,DOG,BIRD,FISH,SNAKE)
I NEED A FORMULA TO COUNT THE TEXT BUT DO NOT INCLUDE
DUPLICATES.

YANECKC


Posted by Malc on April 15, 2001 5:14 PM

Assume your list is in Column A starting in A1
In column B1 enter the formula
=if(iserror(Match(A1,$A$1:A1,0)),1,0)
drag the formula down the list and then sum up the 1's

Posted by YANECKC on April 15, 2001 5:45 PM

MALC

I INPUT FORMULA IN COLUMN B AND DRAG DOWN BUT
IT RETURNS ALL ZERO IN COLUMN B

YANECKC

Posted by Dave Hawley on April 15, 2001 5:59 PM

Hi Yaneckc

Try this one and drag it down.
=IF(COUNTIF($A$1:$A$1000,A1)=1,1,0)


A better way though maybe to use a very simple Pivot Table ?

Dave


OzGrid Business Applications

Posted by YANECKC on April 15, 2001 6:33 PM

DAVE
WHEN I INPUT THAT FORMULA IT DOES NOT COUNT
CAT AND FISH.
WHAT I WANT TO DO IS I HAVE A LARGE SPREADSHEET WITH TEXT IN COLUMN A. I WANT COUNT THE TEXT IN COLUMN A. IF THERE IS A DUPLICATE OR MORE COUNT IT ONE TIME. MEANING IF CAT IS LIST TEN TIMES ONLY COUNT CAT ONCE.

YANECKC


Posted by Arrays Rule OK on April 15, 2001 6:49 PM


At the risk of attracting anyone's wrath for suggesting an array formula, this array formula (enter with Ctrl+Shift+Enter) should work :-
=SUM(1/COUNTIF(A1:A1000,A1:A1000))


Posted by Dave Hawley on April 15, 2001 6:52 PM

Ok, gotcha now!
Leave the formula in cell B1 and in B2 put this:

=IF(AND(COUNTIF($A$1:$A$1000,A2)>1,COUNTIF($A$1:A1,A2)=0),1,0)

Copy this down as far as needed.

Dave

OzGrid Business Applications

Posted by Dave Hawley on April 15, 2001 7:02 PM

Oops...

I may have misread you again :o)

Somebody, I'm not too sure who, has proposed an array that will do it. I do know it wasn't Mark W..... or Aladin though, as they have no idea when to use arrays and when not to (hence my warning about arrays on my Website). In this case an array is a good idea.

You could also get a list of all unique entries in your list by using the Advanced filter set to "Unique Value Only" let me know if interested.


Davwe
OzGrid Business Applications

Posted by A. R. OK on April 15, 2001 7:32 PM

But........


The formula I suggested will only work on the range A1:A1000 if all cells are not blank. This one will be better - the data does not have to populate all cells in A1:A1000.
=SUM(IF(COUNTIF(A1:A1000,A1:A1000)=0,"",1/COUNTIF(A1:A1000,A1:A1000)))

Posted by YANECKC on April 15, 2001 7:58 PM

Re: But........


THANK YOU
THAT WAS EXACTLY WHAT I WAS LOOKING FOR.
YANECKC

Posted by Malc on April 15, 2001 9:53 PM

Re: But........

Hmmm must be Easter Monday or Sunday whereever you are.
Anyway
So why didn't the match thing work?

Posted by Aladin Akyurek on April 16, 2001 2:04 AM

Re: But........

Malc

The last 2 args of IF had to be switched for it to work. An unfortunate accident.

Aladin


Posted by Aladin Akyurek on April 16, 2001 2:19 AM

Dishing out again...

WHY DIDN'T YOU PROPOSE ONE YOURSELF THEN? YOU DIDN'T HAVE TIME TO RUSH TO YOUR WEBSITE, I SUPPOSE.


Posted by Dave Hawley on April 16, 2001 2:30 AM

Oooohh! aren't we getting touchy

WHY DIDN'T YOU PROPOSE ONE YOURSELF THEN? YOU DIDN'T HAVE TIME TO RUSH TO YOUR WEBSITE, I SUPPOSE.

Don't worry, you bum chum will be back soon to support you.

BIG kiss Dave XXXXXXXX

Posted by Annie on April 16, 2001 2:46 AM

Re: But........

The last 2 args of IF had to be switched for it to work. An unfortunate accident. Aladin

But won't that just return all 1's instead of all 0's ?

Posted by Aladin Akyurek on April 16, 2001 3:32 AM

Syntax

Annie

You're absolutely right. It wouldn't solve YANECKC's problem. I was just concerned with the syntax & I should have said so.

To make it solve the problem, we can do the following:

Assuming the words in A from A1 on:

in B1 enter: =IF(LEN(A1)>0,IF(ISNUMBER(MATCH(A1,$A$1:A1,0)),1,0),"")

in B2 enter: =IF(LEN(A2),IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),0,1),"") [ Copy down this as far as needed ]

in C1 enter: =SUM(B:B)

You can also use VLOOKUP instead of MATCH:

in B1 enter: =IF(LEN(A1)>0,IF(ISNA(VLOOKUP(A1,$A$1:A1,0)),0,1),"")

in B2 enter: =IF(LEN(A2)>0,IF(ISNA(VLOOKUP(A2,$A$1:A1,1,0)),1,0),"") [ Copy down this as far as needed ]

in C1 enter: =SUM(B:B)

However, I'd rather prefer using an array-formula in this case.

Posted by Inappropriate on April 16, 2001 4:40 AM

Why don't Dave, Mark W and Aladin meet up for a fight?

Isn't this inappropriate behaviour on a website made for giving out advice to users of Excel? If Dave is attempting to promote his business then he's not doing himself any favours. I for one would not approach him on a professional basis if he refers to other knowledgeale and helpful contributors to this site as "bum chums". Also why doesn't Mark W just fuck off somewhere else?

Posted by Dave Hawley on April 16, 2001 5:28 AM

Re: Why don't Dave, Mark W and Aladin meet up for a fight?

Your dead right, I'm too easily baited at times! You have made a good observation. I will ignore them from now on.


Dave
OzGrid Business Applications

Posted by Simon on April 16, 2001 5:40 AM

Pardon me for butting in, but another question along similar lines....

I want to count the number of times specific text values appear in a column. Ok I can do this with a column per text value searched for down the side of the column I am querying, but it's not very neat and should I want to extend the list I have to also extend the extra columns.

Is there a function that will just allow me to enter the range and the value sought and return a numeric total? I've been trying to make sense of DCOUNTA etc. but with no luck - am I off track? What is the simple answer?? Help!

Posted by Anon on April 16, 2001 6:07 AM

I want to count the number of times specific text values appear in a column. Ok I can do this with a column per text value searched for down the side of the column I am querying, but it's not very neat and should I want to extend the list I have to also extend the extra columns. Is there a function that will just allow me to enter the range and the value sought and return a numeric total? I've been trying to make sense of DCOUNTA etc. but with no luck - am I off track? What is the simple answer?? Help!


=COUNTIF(A1:A1000,"text")


Posted by messageboard lover on April 16, 2001 8:15 AM

Re: Why don't Dave, Mark W and Aladin meet up for a fight?

I don't think that ignoring other contributors would be a good idea. Just look backwards in posts and you will find many answers that were solved only after everybody's answer. Maybe if all of you try to improve each other's answers instead of catchin the errors and laugh at it, the help that we need would be more accurate and more appreciated. Well, it's only an opinion.

Posted by Bruce on April 16, 2001 1:09 PM

I like Mark and Dave's contributions. I've learned from both of them.

I would not want to see anyone leave this board. I have learned a great deal from Mark. I look forward to reading his responses, and then trying his suggestions.

I've also learned a lot from Dave, and visited his web site.

I check the message board daily, and always look for responses posted by Mark and Dave. I know that by reading their responses, there is a good chance I am going to learn something new.

Keep posting Mark and Dave. I appreciate you guys.


Posted by Malc on April 17, 2001 12:38 AM

Re: Syntax

You're absolutely right. It wouldn't solve YANECKC's problem. I was just concerned with the syntax & I should have said so. To make it solve the problem, we can do the following: Assuming the words in A from A1 on: in B1 enter: =IF(LEN(A1)>0,IF(ISNUMBER(MATCH(A1,$A$1:A1,0)),1,0),"") in B2 enter: =IF(LEN(A2),IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),0,1),"") [ Copy down this as far as needed ] in C1 enter: =SUM(B:B) You can also use VLOOKUP instead of MATCH: in B1 enter: =IF(LEN(A1)>0,IF(ISNA(VLOOKUP(A1,$A$1:A1,0)),0,1),"") in B2 enter: =IF(LEN(A2)>0,IF(ISNA(VLOOKUP(A2,$A$1:A1,1,0)),1,0),"") [ Copy down this as far as needed ] in C1 enter: =SUM(B:B) However, I'd rather prefer using an array-formula in this case.

Not a syntax problem or need a complicated formula just needed me to go work and try it out.In B2 enter the formula. B1 is of course 1
=IF(ISNA(MATCH(A2,$A$1:A1,0)),1,0)
and drag it down then sum up the ones

Posted by JRW on April 17, 2001 8:50 AM

Or use A. R. OK's single cell array formula !

Posted by Aladin Akyurek on April 17, 2001 9:19 AM

Re: Syntax

=============================

Malc

You have now almost the same formulation with MATCH that I put forward in response to Annie.

You use ISNA where I use ISNUMBER. They are equivalent in effect. My preference is towards ISNUMBER in that it reminds me of the fact that MATCH returns (if successful) a number indicating the location of the item in a one-dimensional list (a vector or an array). You appear to think that my wrapping of MATCH into an IF in order to check length of the items to be evaluated results in a complicated system of formulas. But, that is needed. Also Otherwise, we'll count a blank as a distinct word. If that's desired (I don't believe that it is), you can simply drop the IF around my version.

My observation about the syntax wasn't incorrect (and not meant as offense), because I believed you were hinting at a solution similar to ones we are now discussing about.

Regards.

Aladin