Database functions to find unique entries on a date.

  • Thread starter Thread starter Chrisso
  • Start date Start date
C

Chrisso

Hi All

I have some data as shown below in an Excel worksheet. Date and name -
each in seperate columns:

Date Name
1/1/8 Chris
1/1/8 Sam
1/1/8 Chris
1/1/8 Dave
1/1/8 Chris

I am using database functions (i.e DCOUNTA etc) to analyse this data.

However I cannot work out how to calculate the number of unique names
on a date.

That is, from the above data the answer I want for 1/1/8 is 3 (Chris,
Sam & Dave).

Does anyone have any clues for me how I can use database functions
(DCOUNTA etc) to arrive at this answer? The names are dynamic and will
vary all the time so I cannot just count known names on a day.

Cheers for any ideas,
Chrisso
 
Hi All

I have some data as shown below in an Excel worksheet. Date and name -
each in seperate columns:

Date  Name
1/1/8  Chris
1/1/8  Sam
1/1/8  Chris
1/1/8  Dave
1/1/8  Chris

I am using database functions (i.e DCOUNTA etc) to analyse this data.

However I cannot work out how to calculate the number of unique names
on a date.

That is, from the above data the answer I want for 1/1/8 is 3 (Chris,
Sam & Dave).

Does anyone have any clues for me how I can use database functions
(DCOUNTA etc) to arrive at this answer? The names are dynamic and will
vary all the time so I cannot just count known names on a day.

Cheers for any ideas,
Chrisso
Click to expand...


=SUM(IF(FREQUENCY(IF(LEN(A2:A11)>0;MATCH(A2:A11;A2:A11;0);""); IF(LEN
(A2:A11)>0;MATCH(A2:A11;A2:A11;0);""))>0;1))

would do the trick as an array function (Ctrl + Shift + Enter)
 
Assumed Col B has no spaces then use

=IF(A2:A6=DATE(2008,1,1),SUM(1/COUNTIF(B2:B6,B2:B6))) ( use ctrl +
shift + enter )
 
Back
Top