| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
