Counting number of times each unique entry is there

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

Hey

What is the code or formula to look in a range(range
A1:A200) and count the number of times each unique entry
is listed? I would like for the value name to be listed
next to the first unique value in columnB and then the
number of times that the value is listed next to that
value in columnC.

For example in range A1:A10 there is a list of data: dog
dog dog cat mouse mouse trap trap trap trap.
In this case the code would need to put the value dog in
B1 and 3 in C1(because there are 3 listings of the value
dog. Next it needs to put the value cat in B4 and 1 in
C4. Next it needs to put that value mouse in B5 and 2 in
C5. And so on...


Thank you in advance

Todd Huttenstine
 
Hi
try the following formula in B1
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$10,A1),"")
and copy down
 
For your exmaple, this array entered (ctrl+shift+enter):

=SUM(1/COUNTIF(A1:A10,A1:A10))
 
Oops, I didn't read the spec correctly, did I?

Then again, in this case, neither did you Frank :-(

OP wants:
dog in B1 and 3 in C1

Frank's solution gives:
3 in B1 and nothing in C1

OP wants:
cat in B4 and 1 in C4

Frank's solution gives:
1 in B4 and nothing in C4

Wer im Glashaus sitzt soll nicht mit Steinen werfen!

I guess we've both lost this particular contract. Better luck next time :-)

--
 
Oops, I didn't read the spec correctly, did I?
Then again, in this case, neither did you Frank :-(
Click to expand...

correct :-( I only added the number to the specific line. To addapt my
solution fot the OP: try
B1:
=IF(C1<>"",A1,"")
and copy down

C1:
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$10,A1),"")
and copy down
Wer im Glashaus sitzt soll nicht mit Steinen werfen!
Click to expand...

lol
just curious: also coming from Germany or just having German as a
second language?
I guess we've both lost this particular contract. Better luck next
Click to expand...
time :-)

Ack
best regards
Frank
 
Frank Kabel said:
lol
just curious: also coming from Germany or just having German as a
second language?
Click to expand...

Neither. I guessed our respective first languages share a proverb so I
Googled "glass houses" +German !!

--
 
Back
Top