Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
I have a sheet, with each row containing one record. Column A contains a
date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
Try something like this:
With this structure in A1:A16 Date Dept 1/1/2007 A 1/1/2007 A 1/1/2007 A 1/1/2007 B 1/1/2007 C 1/1/2007 C 1/1/2007 C 1/15/2007 A 1/15/2007 B 1/15/2007 C 1/15/2007 C 2/1/2007 A 2/1/2007 A 2/1/2007 B 2/1/2007 B And... E1: (StartDate eg: 01/01/2007) F1: (EndDate eg: 01/31/2007) G1: (DeptName eg: A) Then this formula returns the number of unique dates in that range where the DeptName matches the name in G1: =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0))) Using the above example, the formula returns: 2 Dept A appears at least once with 1/1/2007 and 1/15/2007 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
Hi Don, Ron
Thanks for your suggestions. I tried them but they did not work. I forgot to mention that the data may not always be sorted by date. I hope that does not affect your logic? To give you extra info on what went wrong: the formula of Don gave me an extremely high number. For example: I had data for one calendar year but the result of Don's formula was more than 366. This is not possible because there are only 366 unique dates in a year. The formula of Ron returned zero as a result. Thanks. "Ron Coderre" wrote: Try something like this: With this structure in A1:A16 Date Dept 1/1/2007 A 1/1/2007 A 1/1/2007 A 1/1/2007 B 1/1/2007 C 1/1/2007 C 1/1/2007 C 1/15/2007 A 1/15/2007 B 1/15/2007 C 1/15/2007 C 2/1/2007 A 2/1/2007 A 2/1/2007 B 2/1/2007 B And... E1: (StartDate eg: 01/01/2007) F1: (EndDate eg: 01/31/2007) G1: (DeptName eg: A) Then this formula returns the number of unique dates in that range where the DeptName matches the name in G1: =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0))) Using the above example, the formula returns: 2 Dept A appears at least once with 1/1/2007 and 1/15/2007 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
I tried scrambling the dates and DeptNames and I still get correct (to me)
results. Try this: Play with my posted example and see if it gives correct results under those conditions. -If yes ....Try putting some of your data into the example and test again. -If No, then the data is the problem and you'll need to post some sample data so we can see what you're dealing with? Either way, please update us on your progress. *********** Regards, Ron XL2003, WinXP "DKS" wrote: Hi Don, Ron Thanks for your suggestions. I tried them but they did not work. I forgot to mention that the data may not always be sorted by date. I hope that does not affect your logic? To give you extra info on what went wrong: the formula of Don gave me an extremely high number. For example: I had data for one calendar year but the result of Don's formula was more than 366. This is not possible because there are only 366 unique dates in a year. The formula of Ron returned zero as a result. Thanks. "Ron Coderre" wrote: Try something like this: With this structure in A1:A16 Date Dept 1/1/2007 A 1/1/2007 A 1/1/2007 A 1/1/2007 B 1/1/2007 C 1/1/2007 C 1/1/2007 C 1/15/2007 A 1/15/2007 B 1/15/2007 C 1/15/2007 C 2/1/2007 A 2/1/2007 A 2/1/2007 B 2/1/2007 B And... E1: (StartDate eg: 01/01/2007) F1: (EndDate eg: 01/31/2007) G1: (DeptName eg: A) Then this formula returns the number of unique dates in that range where the DeptName matches the name in G1: =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0))) Using the above example, the formula returns: 2 Dept A appears at least once with 1/1/2007 and 1/15/2007 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
Ron,
Your code worked. I had made a mistake whilst typing the formula you gave. Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000 to signify the full range. And apparently with that it did not work. But now I have corrected the formula and it seems to be working like charm. I still have to test with time-stamps but I believe it should work. thanks for your help. "Ron Coderre" wrote: I tried scrambling the dates and DeptNames and I still get correct (to me) results. Try this: Play with my posted example and see if it gives correct results under those conditions. -If yes ...Try putting some of your data into the example and test again. -If No, then the data is the problem and you'll need to post some sample data so we can see what you're dealing with? Either way, please update us on your progress. *********** Regards, Ron XL2003, WinXP "DKS" wrote: Hi Don, Ron Thanks for your suggestions. I tried them but they did not work. I forgot to mention that the data may not always be sorted by date. I hope that does not affect your logic? To give you extra info on what went wrong: the formula of Don gave me an extremely high number. For example: I had data for one calendar year but the result of Don's formula was more than 366. This is not possible because there are only 366 unique dates in a year. The formula of Ron returned zero as a result. Thanks. "Ron Coderre" wrote: Try something like this: With this structure in A1:A16 Date Dept 1/1/2007 A 1/1/2007 A 1/1/2007 A 1/1/2007 B 1/1/2007 C 1/1/2007 C 1/1/2007 C 1/15/2007 A 1/15/2007 B 1/15/2007 C 1/15/2007 C 2/1/2007 A 2/1/2007 A 2/1/2007 B 2/1/2007 B And... E1: (StartDate eg: 01/01/2007) F1: (EndDate eg: 01/31/2007) G1: (DeptName eg: A) Then this formula returns the number of unique dates in that range where the DeptName matches the name in G1: =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0))) Using the above example, the formula returns: 2 Dept A appears at least once with 1/1/2007 and 1/15/2007 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
First test with timestamp included in the date, and the date being one of the
extremes being tested and the formula does not work. For example: in your test-data hereunder if I put only one record for dept A for date March 15; and if I test for Jan 1st till Marc 15th for dept A, I get one number less than the actual number. Just FYI. "DKS" wrote: Ron, Your code worked. I had made a mistake whilst typing the formula you gave. Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000 to signify the full range. And apparently with that it did not work. But now I have corrected the formula and it seems to be working like charm. I still have to test with time-stamps but I believe it should work. thanks for your help. "Ron Coderre" wrote: I tried scrambling the dates and DeptNames and I still get correct (to me) results. Try this: Play with my posted example and see if it gives correct results under those conditions. -If yes ...Try putting some of your data into the example and test again. -If No, then the data is the problem and you'll need to post some sample data so we can see what you're dealing with? Either way, please update us on your progress. *********** Regards, Ron XL2003, WinXP "DKS" wrote: Hi Don, Ron Thanks for your suggestions. I tried them but they did not work. I forgot to mention that the data may not always be sorted by date. I hope that does not affect your logic? To give you extra info on what went wrong: the formula of Don gave me an extremely high number. For example: I had data for one calendar year but the result of Don's formula was more than 366. This is not possible because there are only 366 unique dates in a year. The formula of Ron returned zero as a result. Thanks. "Ron Coderre" wrote: Try something like this: With this structure in A1:A16 Date Dept 1/1/2007 A 1/1/2007 A 1/1/2007 A 1/1/2007 B 1/1/2007 C 1/1/2007 C 1/1/2007 C 1/15/2007 A 1/15/2007 B 1/15/2007 C 1/15/2007 C 2/1/2007 A 2/1/2007 A 2/1/2007 B 2/1/2007 B And... E1: (StartDate eg: 01/01/2007) F1: (EndDate eg: 01/31/2007) G1: (DeptName eg: A) Then this formula returns the number of unique dates in that range where the DeptName matches the name in G1: =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0))) Using the above example, the formula returns: 2 Dept A appears at least once with 1/1/2007 and 1/15/2007 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique items based on date
Try this:
=SUM(IF(FREQUENCY(IF((Dept="A")*(TEXT(Date,"mmmyy" )="Jan07"),INT(Date)),INT(Date))0,1)) "DKS" wrote: I have a sheet, with each row containing one record. Column A contains a date, and column B contains a department. The contents of the column A (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g. "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column A + Column B. I needed (ideally) a worksheet function that would allow me to obtain the following information: Based on a START DATE, END DATE and DEPT NAME return to me the number of times I have the DEPT NAME occuring in my data for between the START DATE and END DATE (both dates included) but counting multiple rows for the same date as only 1 record. Thus if I have 27 rows for the same department with date July 15, then it should give me only the value 1. The time-part in the timestamp format can be ignored. It exists because of input data coming from various sources and some sources store time and some do not store time. How could I do the above with a worksheet function? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting unique items...please help | Excel Worksheet Functions | |||
counting unique items(values or text) | Excel Worksheet Functions | |||
Counting unique records based on date range | Excel Worksheet Functions | |||
counting unique items | Excel Discussion (Misc queries) |