Count Unique dates for XYZ

[jveyron]

Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price

 

[SteveO59L]

Try a Pivot Table

 

[T. Valko]

Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price

Assuming the date/time is a true Excel date/time (numeric value).

Data in the range A2:B6.

D2 = Name1

Enter this array formula** in E2:

=SUM(IF(FREQUENCY(IF(B$2:B$6=D2,INT(A$2:A$6)),INT(A$2:A$6)),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Aladin Akyurek]

Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price

E2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=D2,
   MATCH(INT($A$2:$A$6),INT($A$2:$A$6),0))),
    ROW($A$2:$A$6)-ROW($A$2)+1),1))

where D2 has a name of interest like Name 1 and A2:B6 houses the sample you posted.