Counting unique values

[jurgmay]

Hi there!

I am trying to count the number of unique referrals for numerous
agencies. Using the table below....

Agent....Ref
1..........1
1..........1
2..........1
2..........2
2..........2
3..........1
4..........1
4..........1
4..........2
4..........2
4..........3

...the result should be...

1..........1
2..........2
3..........1
4..........3

How can this be done? I have over 7000 entries to process!

I've tried filtering and Pivot Tables, both without success. Any
pointers would be gratefully received!

Many thanks,

Juerg

 

[Bernie Deitrick]

One way is to create a new table by selecting your original table and using Data / Filter /Advanced
Filter... Check unique records only and copy to antother location, then use the pivot table on that
unique table.

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

If you really can't use Pivot Tables, the formula approach could be

D1:=A2
D2: =IF(ISERROR(MATCH(0,COUNTIF(D$13,$A$2:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH(0,COUNTIF(D$13,$A$2:$A$2
0&""),0)))
E1:
=SUM(--(FREQUENCY(IF($A$2:$A$20=D1,MATCH($B$2:$B$20,$B$2:$B$20,0)),ROW(INDIR
ECT("1:"&ROWS($B$2:$B$20))))>0))

D2 and E1 are array formulae, and should be committed with Ctrl-Shift-Enter,
not just Enter. Then copy them down as far as is needed.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Domenic]

Assuming that A2:B12 contains the data, let D25 contain 1, 2, 3, and
4. Then enter the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, in E2 and copy down:

=COUNT(1/FREQUENCY(IF($A$2:$A$12=D2,$B$2:$B$12),IF($A$2:$A$12=D2,$B$2:$B$
12)))

Hope this helps!

 

[jurgmay]

Thanks for replies everyone!

I'm more confused than I was before!!!! I'm not completely sure what t
do with the formula-based repsonses. I've tried them and can't get the
to work... they do SOMETHING just not what I expected! I dare say I'
doing something wrong.

Re. Pivot Tables.
Can you use Pivot Tables with only 2 columns!? I obviously don'
understand how they work but I'm really trying! Honest!!!

This all seems a lot more complicated than I expected! I've only bee
used Excel for a short while and feel like I'm drowning all of
sudden!


Thanks all,

Juer

 

[Domenic]

Did you confirm the formulas with CONTROL+SHIFT+ENTER? After typing the
formula, press the CONTROL and SHIFT keys down, then, while those two
keys are pressed down, press the ENTER key. Excel will place braces {}
around the formula, indicating that you've entered the formula correctly.

Does this help?

 

[aidan.heritage]

Yes, you can use Pivot Tables with only two columns - you can even do
them with one!