Hi all,

I feel there is a simple solution to this problem but I can't figure it out, i wonder if you guys can help me?

Column A: Column B: Column C: Column D:

11 A A
22 A B
33 B C
44 C D
55 C
55 D


Column C is blank. Column D is Column B having removed duplicates.

In column E, I want a formula that indicates where each corresponding letter in Column D, has more than one column A value. I.e. E1 should read 2 because of 11 in A1 and 22 in A2.

And the rest should read E2=1, E3=2, E4=D.

I hope this makes sense.

I look forward to your help, Thanks

I have just realised this didnt show up how I wanted it to, so I will post a screenshot. Again, I am looking for the formula to insert into column E to provide the values in red.

Thanks

On Tuesday, July 17, 2012 5:08:57 AM UTC-5, VZBoon wrote:
I have just realised this didnt show up how I wanted it to, so I will
post a screenshot. Again, I am looking for the formula to insert into
column E to provide the values in red.

Thanks


+-------------------------------------------------------------------+
|Filename: screenshot.png |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=470|
+-------------------------------------------------------------------+



--
VZBoon

If you are saying you want to count the unique letters then simply look in the help index for COUNTIF.

"VZBoon" wrote:
I am looking for the formula to insert into
column E to provide the values in red.
[....]
http://www.excelbanter.com/attachment.php?attachmentid=470|

Put the following formula into E1 and copy down:

=COUNTIF($B$1:$B$6,D1)

PS.... I wrote:
"VZBoon" wrote:
I am looking for the formula to insert into
column E to provide the values in red.
[....]
http://www.excelbanter.com/attachment.php?attachmentid=470|

Put the following formula into E1 and copy down:

=COUNTIF($B$1:$B$6,D1)

I mean to say: assuming the value in E4 is a typo, and it should be 1.

If it is not a typo, you will need to explain why E4 is 2. It might have
something to do with 55 in both A5 (next to "C" in B5) and A6 (next to "D"
in B6). But in that case, the counting rule for column E is unclear. There
are many possible interpretations of the limited example.

Hi Thanks for the reply.

Yes you're right it was a typo. However this has not fully solved my problem. I'm finding it hard to explain, but i will give it a go: I need to count the number of unique A values against each unique B value. i.e. if there is duplication of A's against B's, this will show up as 1.

See extended example provided.

I really appreciate your help.

Thanks