Summing and removing duplicates

[Guest]

Hi I need help on a formula if possible:

I have a data entry sheet which has "Date" and "Train Number" as labels.
There can be any number of entries for a day and the same train might appear
more than once in a day.

I need to calculate the number of trains per day without the duplicates

e.g.
DATE TRAIN No.
5/1/07 844
5/1/07 324
5/1/07 844
6/1/07 546
6/1/07 546
6/1/07 546
7/1/07 324
7/1/07 844
7/1/07 546

Should return a display of:
5/1/07 2
6/1/07 1
7/1/07 3

Many thanks for your help!

Marley

 

[Bob Phillips]

E1: =A2
E2:
=IF(ISERROR(MATCH(0,COUNTIF(E$1:E1,$A$2:$A$20&""),0)),"",INDEX(IF(ISBLANK($A
$2:$A$20),"",$A$2:$A$20),MATCH(0,COUNTIF(E$1:E1,$A$2:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

F1:
=SUM(--(FREQUENCY(IF(A1:A20=E1,MATCH(B1:B20,B1:B20,0)),ROW(INDIRECT("1:"&ROW
S(B1:B20))))>0))

also an array formula.

Copy E2 and F1 down.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Marley,

I came across an excellent article from Microsoft:

http://office.microsoft.com/en-us/e...e number of unique values by using functions

 

[Guest]

Thanks, I had seen this and it was really good but I need to add some other
level of "if" into the equation so that the questions becomes: "If the record
matches a certain date, then count the number of unique entries for this date
alone" ..... and that'a where it gets beyond me! Any ideas?

 

[Bob Phillips]

Check my response.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Err... soory Bob ... which response am I supposed to be checking?