Counting Duplicate Values

[Knight of Nee]

Hi

I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.

e.g

if in cell A1 i have = "apples"
and in cell A2 i have "apples"
and in cell A3 i have "Pear"

i want this to return the value 2 not 3, make sense?

how can this be done via a formulae or macro?

Thanks

 

[Marcelo Branco]

The data must start at row 2 (let row 1 empty) for the formula works.

Try in E2 (assuming your data begin in row 2)

=IF(COUNTIF($B$2:B2;B2)=1;"";C2-LOOKUP(2;1/($B$1:B1=B2);$C$1:C1))

and in F2

=IF(COUNTIF($B$2:B2;B2)=1;"";D2-LOOKUP(2;1/($B$1:B1=B2);$D$1:D1))

M.

 

[MikeJK]

The data must start at row 2 (let row 1 empty) for the formula works.

Try in E2 (assuming your data begin in row 2)

=IF(COUNTIF($B$2:B2;B2)=1;"";C2-LOOKUP(2;1/($B$1:B1=B2);$C$1:C1))

and in F2

=IF(COUNTIF($B$2:B2;B2)=1;"";D2-LOOKUP(2;1/($B$1:B1=B2);$D$1:D1))

M.

When I first tried, I forgot "LOOKUP(2 ; <-- " to change that

And I have the meaning of column at 1 yes, so the values begin from 2.

The beginning was same what I thought yesterday, but I didn't know about LOOKUP, so that was it..

Thanks to both of you for your effort
Now it works perfectly..

-Mike

 

[MikeJK]

Hmm, with minor changes, would it be possible to easily track the values with duplicates?

In situation with same chart, but a new factor; is the items sold yet.

I have made a formula to see from M (quantity bought) and N (quantity sold) to colour the whole row red if there's still bought items left.. But I would like to track if the current buying price and selling prices changes while I still have stock, so I could see the affection of market price change..

 

[julia55]

k

 

[Robert Mika]

I have a similar issue to resolve, and I've tried the formulas listed for the other instances with slight modification, and they don't work. I'm not that well versed in excel, so please excuse me if I'm overlooking something simple...
I have a spreadsheet that I use to track the number of work items I do every day. My problem is that when I verify the work of someone else (that's my job) I give a count to myself, but if they do more than one thing for that item, I give them two counts. Until now, I've had two columns, and I paste the location # of the work item into a column for me, and one for each person that gives me work. I'd like to cut out the extra column, so what I'd like to do is to have a formula that will count the total # of location #'s in a column not counting the duplicates so I can pull the count for me out of the count for the other people...
Thanks for your help.

You shoudl have opened new topic.
(I have reported that to moderator)
Look here for count if unique item.
http://www.xl-central.com/count-unique-values.html