Database functions to find unique entries on a date.

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
0
12/6/2008 9:35:07 AM
excel 39879 articles. 2 followers. Follow

2 Replies
451 Views

Similar Articles

[PageSpeed] 29

On 6 joulu, 11:35, Chrisso <chris75sut...@gmail.com> wrote:
> Hi All
>
> I have some data as shown below in an Excel worksheet. Date and name -
> each in seperate columns:
>
> Date =A0Name
> 1/1/8 =A0Chris
> 1/1/8 =A0Sam
> 1/1/8 =A0Chris
> 1/1/8 =A0Dave
> 1/1/8 =A0Chris
>
> 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


=3DSUM(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)
0
12/6/2008 10:32:42 AM
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 )



Chrisso wrote:

> 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
0
muddanmadhu (119)
12/6/2008 2:51:51 PM
Reply:

Similar Artilces:

Opening Access Database from Internet Explorer
sWe currently have a site where we can upload files and then a user can access these files. Currently we have an access database which when you click on the link you get the option to either save it or open it. When you click Open the Access database opens just fine on the machines that have Access 2003 but not the case for the machines that have Access 2007. At first I thought it was because I was trying to open a .mdb on a machine that has Access 2007 but then I created an Access 2007 database and the same error when I tried to open it through the site. Is anyone aware of c...

Missing Entries in Global Address Book
One of my clients is running Outlook 2003 with a SBS server 2003 and Exchange. His workstation is XP PRO sp2. He is missing one entry from the Global Address Book set-up on the server. Also, he sees entries that are flagged on the server as to * not * to be shown in the Global Address Book. All assistance is appreciated. -- Regards, Allan C Check the RUS http://support.microsoft.com/default.aspx?scid=kb;en-us;q253828 -- Teo Heras "Allan C" wrote: > One of my clients is running Outlook 2003 with a SBS server 2003 and > Exchange. > His workstation is XP PRO s...

Preventing Duplicate Entries Across Cells
Anyone know how to prevent duplicate entries of numbers. e.g. I have two sheets. I want to make sure that the same number is not entered more than once. Thank you. I think this might work- Have a countif funtion (of pair of for 2 sheets) that look a th current row and see if it has duplicate entries ie a b 1 1 =COUNTIF($A$1:$A$2,A1) - 1 2 2 =COUNTIF($A$1:$A$2,A2) - 1 (the minus one has been added so when a number exists only once it wil return 0. An if statement may also be need for if hte cell is blank.) Then add this behi...

where can i find credit notes
i can do a invoice but need to do a creditor note. where can i find it? ...

No dates
when ever i recieved an e-mail the date it was sent was always along side the e-mail, but know i dont get a date, why has this happenend, i like the date being printed as i know when i recieved them please help. Is there something i can do. "ANNA B." <bryla@tiscali.co.uk> wrote in message news:%23htLYe01KHA.5828@TK2MSFTNGP02.phx.gbl... > when ever i recieved an e-mail the date it was sent was always along side > the e-mail, > but know i dont get a date, why has this happenend, i like the date being > printed as > i know when i recieved them...

find user's computer name
Hi all, I got macro below which tell me user's computer name who is logged on in current database. What changes I do in below macro that it should tell me the user's machine name who is logged on in the database which is on path "C:\Documents\Record.mdb". Please can any friend can help Private Declare Function apiGetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Private Function fOSMachineName() As String 'Returns the computername Dim lngLen As Long, lngX As Long Dim strCo...

Advanced Find not searching sub-folders of shared Mailbox
I'm using Outlook 2003 to access my mailbox on an Exchange 2003 server (SBS2003) and the shared mailbox of another user. When using Advanced Find on the other mailbox it doesn't search sub-folders but it does on my mailbox. Is there a setting I'm missing? Lester Bennett You're not missing anything. That's a design limitation.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers ht...

trying to use Indirect function
I have a formula in cell G2 of a worksheet called "Departmental Summary" Column A of the same workbook contains employee names and there is a separate worksheet in the file for each employee, with the name of the worksheet being the exact same as the value for the employee in column A. The formula in G2 is: =IF(COLUMN(A2)>MAX('SheetName'!$T:$T),"",INDEX('SheetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T,0))) I would like to replace the SheetName with the Indirect function pointing to cell A2 in the Departmental Summary workshee...

Cannot find root element in XML file
Hi all, I created an XSD to define the structure of an XML file for my project. I made an XML file linked to the XSD using XmlSpy. The problem is that if I read the file using .NET XmlDocument and then query for the root element, the result is always null (1). However if I strip the root element of all attributes generated by XmlSpy, then there is no problem to find the root element with .NET XML classes (2). (1) The XML for which querying root returns null: [code] <?xml version="1.0" encoding="UTF-8"?> <IncidentDefinitions xmlns="http://tempuri.org/XML...

Do dropdown lists have an autocomplete function?
We have created a course registration form for our students. To insure proper spelling and wording of course numbers and names, we have a drop down list that has grown to about 160 course titles. The registrar has to scroll through a large number of courses in the drop down list to get to, for example, "PSY 101 An Introduction to Psychology". Is there some way to include an autocomplete function so that when the registrar enters the cell with the drop down list and begins typing PSY 10... the drop down list will automatically go to PSY 101 An Intro...? I thank in advance whoev...

Data Validation / Cell Entry
Hi Is there a way I can force a user to make an entry in a cell without using macros/code? EG: If the result of cell A1 = "XYZ" can I force them to make an entry in cell B1. ( If cell A1 = "" then no entry is required in B1). Hope that makes sense. Thanks Steve This won't force the user, but it will make it obvious. Format C1 in nice bold red letters and put a formula in that cell like: =if(and(a1="",b1<>""),"Please clear B1",if(a1="xyz",b1=""), "please put something in B1","") (...

Error:Object expected , Function:setFocus()
Hi, I am getting a pop up msg. box while saving an activity or incident with values in any lookup fields(ex: From, To ,Regarding frields in Activity). This start happening after UR7 upgrade. when I keep all lookup fields empty and save it's successfull. Please let me know if anybody faced the similar error and got resolved. Error details I am seeing when clicking on 'View the date that will be sent to Microsoft' is, Microsoft Dynamics CRM Error Report Contents <CrmScriptErrorReport> <ReportVersion>1.0</ReportVersion> <ScriptErrorDetails> <Mess...

help with weekdays between two dates
A little rusty with access - been away for a while. Wondering if someone could help me with determining the weekdays between two dates. a simple function of somekind using the date or count functions? Be great if it did stat holidays too but I know that's asking more than is possible? JT "Jim" <jimt@pioneers.ca> wrote in message news:2rWAj.66322$pM4.15431@pd7urf1no... >A little rusty with access - been away for a while. > > Wondering if someone could help me with determining the weekdays between > two dates. > > a simple function of somekind us...

Finding values in different rows/columns
This should be simple, but I'm lost. To greatly simplify my sheets: 1 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 2 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 I want to do a VLOOKUP(1,A1:A10,1,false) and return the value in D4, or VLOOKUP(2,A1:A10,1,false) and return the value in C9. In other words, find my A cell reference and return the value in a different row/column from that. Another question: When I move a cell (cell1) to another cell (cell2) a cell (cell3) that references cell1 moves its reference from cell1 to c...

How do I stop a general format from being autoformatted to date
I want to stop Excel from automatically changing the format of a cell from general to time every time I enter a new row of data. I have several column headings running across the top of the page and I have set the format of 4 columns to display the time (HH:MM), the next column has been formatted to show a GENERAL entry. This was done by selecting the whole column and applying the format of General. The problem is when I enter data for a particular row I enter from left to right and put the data in the 4 TIME formatted cells first, by the time I go to the 5th cell which should just be a n...

Survival distribution function chart
Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page http://www.xlstat.com/demoKM.xls are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat Pro. I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this. -- max0d --...

Date Help
I have linked two columns on a worksheet so that when I type in the date in cell C6 it also appears in cell J6. I entered =$C$6 in J6, and this works except sometimes I don’t need to use the date cells. My problem is in cell J6 the date appears as 1/0/00 until I type the date into cell C6. What am I doing wrong? Thanks, Malcolm Hi, use this formula instead =if(C6="","",$C$6) "Malcolm" wrote: > I have linked two columns on a worksheet so that when I type in the date in > cell C6 it also appears in cell J6. I entered =$C$6 in J6, and ...

Double Inbox entries
Hello All, Just shifted from Outlook Express to Outlook, so far the transistion is less than smooth. I am receiveing all inbound e-mails in duplicate. Anyone have any idea why ? How many accounts do you have set up? Are you leaving mail on the server? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips maili...

Calculating entries in a group
I have a database that lists all applicants, and I have a report that sorts them according to which Institution they are attending. How can I have the report display the number of applicants that are attending each Institution. For example , Four applicants from Harvard, 10 Applicants from Columbia, ect. Use this query --- SELECT YourTable.[Institution], Count(YourTable.[Institution]) AS [CountOfInstitution] FROM YourTable GROUP BY YourTable.[Institution]; -- KARL DEWEY Build a little - Test a little "amandap83" wrote: > I have a database that lists all applicants, and ...

Counting days between dates
I am trying to calculate working days late or early between two dates using =NETWORKDAYS(A2,B2) If I have a due date of 24 Sept and I ship on 24 Sep then I am neither late or early so I am on time i.e. 0 days - but my formula returns 1! If I have a due date of 24 Sept and I ship on 23 Sep then I am shipping 1 day early i.e. –1 – but my formula returns –2 formula due actual days early late should be 24-Sep 24-Sep 1 0 24-Sep 25-Sep 1 1 24-Sep 23-Sep -2 -1 What do I need to do...

OL2003 Cannot Edit Calendar Entries
Sync with Palm Zire 21 yielded error message: "following item is in conflict" then referred to several recently created or modified calendar items. Now I cannot do anything to a calendar item (in the main calendar) without an error message, such as: "Cannot save item" or "The item may not have been deleted correctly. If someone sent you the item, that person's copy of the item may not be updated." Other calendars function correclty. I exported the calendar to a PST. The calendar in that PST is empty even though it is 271 kb. What is available to repair a P...

find and replace data in a yes/no field
How do you find and replace a Yes/No field? I want to find all no entries and globally replace them with a yes entry. Any assistance is deeply appreciated. Write a query UPDATE MyTable SET MYyesno = Yes WHERE MYyesno = No; The where clause is unnecessary if every row has a value, since you'll be making them all True anyway. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Frank" <fhsmith7@bellsouth.net> wrote in message news:BvoPi.155$Q6.102@bignews7.bellsouth.net... > > How do you find and replace ...

Automatic changes in cell entries
How does one shut off the abjectly rediculous habit of Excel insisting on knowing better what I want for cell entries than I do? EG If I type AKs I want AKs not Aks... :mad: I have reviewed all the options in Tools|Options as well as other places to no avail. TIA tools-option-edit and the last item <enable autocomplete cell values> uncheck this doco <perdedor@centurytel.net> wrote in message news:#LhMjTA4EHA.3472@TK2MSFTNGP09.phx.gbl... > How does one shut off the abjectly rediculous habit of Excel insisting on > knowing better what I want for cell entries than I ...

how can I find excel 2007 tutoring for beginners
Have excel 2007. Never used excel before and need to create an examination table with various timings for different candidates. I need some basic tutoring. I only seem to be able to find help for experienced users coming to 2007. Use the "Dogpile" search engine and search for 'excel 2007 tutoring for beginners' "m.lewington" wrote: > Have excel 2007. Never used excel before and need to create an examination > table with various timings for different candidates. I need some basic > tutoring. I only seem to be able to find help for experience...

HOW DO I Auto inserting date and time?
Can someone out there please help me! Am trying to find what the formula would be to automatically insert date and time (in seperate columns) when another field has been filled. Ie, if cell C3 has data in it, then for C1 to automatically insert the date, and C2 to automatically insert a time. I am told that this IS possible, but I can't work out how to do it! Thank you in advance for any assistance! Shane --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 thank you...