Counting number of times each unique entry is there

[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

 

[Frank Kabel]

Hi
try the following formula in B1
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$10,A1),"")
and copy down

 

[onedaywhen]

For your exmaple, this array entered (ctrl+shift+enter):

=SUM(1/COUNTIF(A1:A10,A1:A10))

 

[Frank Kabel]

Hi
won't do in this case as the OP wanted and conditional count of unique
entries

 

[onedaywhen]

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

--

 

[Frank Kabel]

Oops, I didn't read the spec correctly, did I?

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

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!

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

time

Ack
best regards
Frank

 

[onedaywhen]

lol
just curious: also coming from Germany or just having German as a
second language?

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

--

 

[Frank Kabel]

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

tricky