Summing and removing duplicates
 

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

Summing and removing duplicates
 

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)),RO W(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)

"Marley" wrote in message
...
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

Summing and removing duplicates
 

Marley,

I came across an excellent article from Microsoft:

http://office.microsoft.com/en-us/ex...20%20functions
--
http://HelpExcel.com




"Marley" wrote:

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

Summing and removing duplicates
 

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?
--
Marley


"galimi" wrote:

Marley,

I came across an excellent article from Microsoft:

http://office.microsoft.com/en-us/ex...20%20functions
--
http://HelpExcel.com




"Marley" wrote:

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

Summing and removing duplicates
 

Check my response.

--
HTH

Bob Phillips

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

"Marley" wrote in message
...
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?
--
Marley


"galimi" wrote:

Marley,

I came across an excellent article from Microsoft:


http://office.microsoft.com/en-us/ex...20%20functions
--
http://HelpExcel.com




"Marley" wrote:

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

Summing and removing duplicates
 

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


"Bob Phillips" wrote:

Check my response.

--
HTH

Bob Phillips

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

"Marley" wrote in message
...
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?
--
Marley


"galimi" wrote:

Marley,

I came across an excellent article from Microsoft:


http://office.microsoft.com/en-us/ex...20%20functions
--
http://HelpExcel.com




"Marley" wrote:

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