CountIf and only different values

[Sir Babydum GBE]

Hi

I have a purchase history log

Suppose in column A I have a list of 10 people and their names appear several times (1 row every for each product they order). So if Bob buys 10 oranges today, 5 apples tomorrow and 6 bananas next week. His name will appear 21 times on my sheet.

Peter might buy 3 bananas. So all in all - Peter's name is on the list 3 times. However, in column 2 is the list of fruit. So we have 10 oranges, 5 apples and 9 bananas

But want I want to do is return the following results next to each occurence of that persons name in column C:

Peter = 1
Bob = 3

Because Bob has bought 3 different types of fruit, Peter bought 1.

In actual fact its not fruit we're dealing with, its codes. So there may be thousands of customers and hundreds of different codes.

Any idea of what formula I need please?

BD

[mdmackillop]

An Advanced Filter can give a table of Unique Values to which you can use a simple CountIf.

[Sir Babydum GBE]

An Advanced Filter can give a table of Unique Values to which you can use a simple CountIf.

Thanks MD. Actually I can't go this route because i need the results to be in the original table as they are used to help construct a pivot table.

But I've been digging aaround the web and there do seem to be some array formulas out there that look promising.

Cheers

BD

[mdmackillop]

Is the columns A-C the result you are after?. If not, can you change it to suit.

[Aflatoon]

Is this perhaps what you wanted?

[redhunter]

If i got it right try :

[vba]Sub Only_Once()
Set rng = Range("A1:B" & Cells(65536, 1).End(xlUp).Row)
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
rng.SpecialCells(xlCellTypeVisible).Select
Range("A1").Select
End Sub[/vba]

[Sir Babydum GBE]

Thanks everyone, I just came back of annual leave - I'll check these out presently

BD

[shrivallabha]

And so will this formula variation based on Aflatoon's layout and data. Needs to be ARRAY Entered (CTRL + SHIFT + ENTER):

=SUM((FREQUENCY(MATCH($A$2:$A$23&$B$2:$B$23,$A$2:$A$23&$B$2:$B$23,0),MATCH( $A$2:$A$23&$B$2:$B$23,$A$2:$A$23&$B$2:$B$23,0))>0)*ISNUMBER(SEARCH(A2,$A$2: $A$24,1)))