Unique entries based on condition - count distinct

Hi,

I'm really struggling with what I thought should be easy.

A                  B                           C 
D
branch       salesperson           transaction num          Item code


I'm trying to the number of unique saples people in each branch, the number 
of unique tranactions by each sales person, and the number of unique items 
in each transaction.

In another program I use "Count Distinct" but am struggling to acheieve the 
same in excel. Any ideas?

--
Rich
http://www.rhodes-lindos.co.uk
http://www.rhodes-pefkos.co.uk



0
Rich
5/1/2010 7:47:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1310 Views

Similar Articles

[PageSpeed] 45

Try these array formulae

=SUM(N(FREQUENCY(IF(A1:A100="branch",MATCH(B1:B100&"",B1:B100&"",0)),MATCH(B1:B100&"",B1:B100&"",0))>0))

SUM(N(FREQUENCY(IF(B1:B100="salesperson",MATCH(C1:C100&"",C1:C100&"",0)),MATCH(C1:C100&"",C1:C100&"",0))>0))

etc.

-- 

HTH

Bob

"Rich" <REPLACEWITHrich@richdavies.com> wrote in message 
news:X%QCn.107521$iL1.106393@newsfe24.iad...
> Hi,
>
> I'm really struggling with what I thought should be easy.
>
> A                  B                           C D
> branch       salesperson           transaction num          Item code
>
>
> I'm trying to the number of unique saples people in each branch, the 
> number of unique tranactions by each sales person, and the number of 
> unique items in each transaction.
>
> In another program I use "Count Distinct" but am struggling to acheieve 
> the same in excel. Any ideas?
>
> --
> Rich
> http://www.rhodes-lindos.co.uk
> http://www.rhodes-pefkos.co.uk
>
>
> 


0
Bob
5/1/2010 9:19:37 AM
Reply:

Similar Artilces:

Counting cells basesd on multiple criteria
Ok so i have two sheets In SHEET 1 column A i have a list of about 1000 different alarms. Then In SHEET 2 i have a log for 30 days with the alarms that occured in those thirty days. The log takes up 35465 thousand rows. Column D has the name of the alarm and Column C has if the alarm was an IN or OUT alarm. What i want to do is in SHEET 1 column 2 display how many times each of these 1000 alarms occured in the last 30 days but only when it was an IN alarm. I cant use something that refrences the name of the alarm i need a formula that can actually refrence the cell because it would be ...

conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 & 2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say 30, the answer would be 4. if the number was say 80, the result would be NA. Can anyone help me with this problem? Thank you and kind regards JV ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** This can probably be done easier =INDEX(LARGE(ROW(INDIRECT("1:"&...

Conditional format to cells containing a #DIV/0! error
I would like to apply a conditional format to an entire spreadsheet. In particular, if a cell returns and error such as #DIV/0! I would the selected font color to be white. What do I have to enter in the conditional formatting menu? If I set the value of the cell equal to #DIV/0!, the format does not work. A solution that I found could be to use the GoTo functions to select the cells with errors and then manually apply the color, but since I am creating a template at a later point the empty cells that return the divided by zero error may contain a number. Basically I would like the cells t...

Automatic Formatting for Calendar Entries
I would like to have different colours for each of the calendar entry types. I understand that I probably need to select View, Current View, Customize Current View and select the Automatic Formatting button but it is looking for a condition. I don't want to enter something like a specific word in the entry to identify it as for example an appointment. I simply want Outlook to see I am creating an appointment and make it blue. Is this possible? Thanks Have the rule look for Outlook Form type -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ O...

Excel 2007, Sort data by no. similar entries?
Is it possible with Excel 2007 to sort data by the number of similar entries within a column? I.E Row1 Row2 1977 Andrew 1980 Andrew 1960 Rachel 1950 Rachel 1940 Rachel 1965 John What I want to be able to do is sort data in Row2 so that Rachel shows 1st as she has 3 entries, then Andrew and finally John as he only has a single instance, is this possible? Thanks, Andrew. How about using a helper column? I read your row1 as column1 and row2 as column2, and put the formula like "=COUNTIF(B$1:B$6,B1)" into C1 and copy it down to C6. then , sort the table b...

'advanced filter/unique values only' inaccurate counts
i often have to use filters to reduce a column of numbers to just it's unique values. simple enough, but unfortunately excel's advanced filter doesn't always do the job and leaves out values or just doesn't filter correctly. i'm hoping there's a simple explanation for this and the filter can be made more reliable. any thoughts? The filters are reliable, so there may be a problem with the data. For example, a space at the end of a text string can cause two cells that look the same to be extracted by the filter. Perhaps you can give an example of what's left out, ...

How do I find the series associated with a given legend entry.
I have a problem, whereby I need to find the association between a legend entry and a chart series (in order that I can do something with the legend entry for a specific series if it exists). I cannot find a way to establish a firm link between a series and its legend, for instance: The chart has 5 series. The legend had 5 series but the user (or something/somebody) deleted 2 of these legend entries so there are 3 legend entries. So, at this point lets say I have code which has a specific series in its hand: Excel.Series series = _chart.Series(3); And with this series I want to del...

alpha-sort list where each entry contains more than one line
How do I alpha-sort a list where each entry contains more than one line? For example, a person's name (line 1) and his organizational affiliation (line 2)? Each technique I try sorts each and every line, so the person gets separated from his organization and the alpha-list is meaningless. The easiest workaround is to use Ctrl-H (Find and Replace) to convert your multi-pagraph entries into single paragraphs. Assuming that each entry has certain similar line headings (Name, Organization, Address, etc.), you could search for "^pOrganization" and replace all occurrenc...

Possible bug in excel 97: impossible to entry dates
In a workbook excel 97 forces calculation when I put in a cell 04/04/2004, ie. excel calculates =4/4/2004. The cell's has date format then the result is jan-01-1900, because it calutaes the quotient 1/200 !!! . Do not matter why I do to resolve the trouble still the same. I I put vgr. 04-04-2004, excel reedit the content in the formula ba adding a "=" sign at the string begin changing to =4-4-2004 result error ###### in date format, of course,but -2004.00 in general format The most curious is if i insert a new sheet in this worbook exc manages well the date and hour input. No ...

Unique identifier
Can someone tell me how I could attach a unique indentifier to an order number. I would like the identifer to increment until the order number changes sort of like the following: Order num Identifier 75621 1 75621 2 75621 3 75622 1 75623 1 75623 2 Thanks for any sugestions. Chris ...

The blank cells i need to count have formilas in
How do i write a fromula to add together diffeent ranges of cells that will count blank cells even if the blank has a formula in it. f 17:f12 + g23:g30+h5:h10 = 8 using excel 2007Vista Hi Rubber, If the blank cells with a formula in them has the value of zero, use COUNTA to count the cells and COUNTIF to count the zero values in each range, then subtract the two. =COUNTA(F12:F17)-COUNTIF(F12:F17,0) or fully =COUNTA(F12:F17)-COUNTIF(F12:F17,0)+COUNTA(g23:g30)-COUNTIF(g23:g30,0)+COUNTA(h5:h10)-COUNTIF(h5:h10,0) Squeaky "Rubber 4 u" wrote: > How do i w...

Specifying source campaign in Opp., not counting as campaign respo
General usage question regarding campaigns. If we create a campaign, then specify that campaign as the source campaign for an opportunity, that opportunity/account is not then listed as a response to that campaign. So when we run a Campaign Performance report, no responses are listed. Do you have to use the normal method of entering a response within the campaign? I'd think that if you can associate a campaign within the opportunity, that linkage would reflect in a Campaign Performance report. Thanks!! ...

Outlook 2003: Rules based on the HTML code
Is there a method, either directly or with the macro language to make rules based on the actual HTML code rather than on the rendered HTML message body. I want to right something that catches some SPAM messages that are random text and images. AFAIK, no, there isn't. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, Dave asked: | Is there a method, either directly or with the macro language to make | rules based on the actual HTML code rather than on the rendered HTML | message body.=20 |=20 | I want t...

Limit choices in one cell dependent based on choice in other cell
Does anybody know if and how to limit the choices in one cell (from a list) based on the choice (from a list) in another cell? For example, if (from a list) they pick "Customer Communication" in say column M, they would only get the choice of three issues (instead of the entire list) in column N. Thanks! Mahaas Debra Dalgleish shows how to use dependent lists with data|validation here: http://contextures.com/xlDataVal02.html michelle.haas@xerox.com wrote: > > Does anybody know if and how to limit the choices in one cell (from a > list) based on the choice (from a list) ...

How to count last name letters
Hi folks, I would like to know how I can count how many records have the same letter. For example, how many records start with the letter A, how many for letter B, etc. Seems simple enough, but I can't figure out how to work this. Would appreciate some help. Thanks. In news:uKD40b2rHHA.3484@TK2MSFTNGP05.phx.gbl, jason <jasonsantos-NOSPAM-@allstatesmedical.com> wrote: > Hi folks, > I would like to know how I can count how many records have the same > letter. For example, how many records start with the letter A, how > many for letter B, etc. > > Seems simple ...

Conditional Delete
I want to delete all cells that contain specific text. Any help would be appreciated! I'd select that column and do Data|Filter|autofilter Then use the dropdown and choose custom contains: (type in your value) And delete the visible rows Then data|Filter|autofilter (to remove the filter) ======= You may be able to chose the value you want from the dropdown, too--avoiding the Custom stuff. gsxith3@embarqmail.com wrote: > > I want to delete all cells that contain specific text. Any help would > be appreciated! -- Dave Peterson Edit>Find what: yourtext Find all. ...

Tab stop in SOP Entry
Hi Is there a way to change the default tabs in Sales Transaction Entry so that it always jumps at the Quantity for each line item? The default quantity is 1 for each line item. Thanks in advance. You will have to use Modifier or have a partner modify the screen for you and then you would need Customization Site License to use the modified screen. "Cesar" wrote: > Hi > Is there a way to change the default tabs in Sales Transaction Entry so that > it always jumps at the Quantity for each line item? > The default quantity is 1 for each line item. > > Thanks...

Asking to save for new entries thorugh SDK
I'm uploading some invoices through a program in c#, but when the user opens the invoice, it asks to save it. Once you do it, it doesn't ask again. I was looking at the record in the InvoiceBase table and looks like the record was not changed. The fields that I use in the invoice are: + name + description + customerid + ownerid and other custom fields. Any idea if any other field must be initialized. It happens the same with the invoice detail, for it I upload: +description + productdescription + actualdeliveryon + quantity + priceperunit (equals 0) + ispriceoverridden (equals 1...

Compare two lists of data to find new entries
I currently run a daily report from a CRM database to monitor my sales teams activities with customers. Each activity has a 'unique' reference. My aim is to compare the report (which is in excel) from one day with the new report to show the new 'activities'. In simple terms I want to say if activity 'x' is in the new list the show the information from that row on a new worksheet so I can see at a glance the new activities. How about an alternative? Insert a new column near that unique reference (say column B is inserted and column A is the unique reference). T...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

Count unique occurrences in a list
Hi guys, I have a list as so: Part1 Run1 Part1 Run1 Part2 Run1 Part3 Run1 Part4 Run2 Part4 Run2 Part1 Run3 Part2 Run3 Part5 Run3 Part6 Run4 Part1 Run5 Part1 Run5 Part1 Run5 Part6 Run5 .... Part1 Run1001 Part3 Run1001 Part3 Run1001 .... The number of unique parts may be >100 and the number of runs can be anything (it's actually ~2000 in my current list) What I would like to do is this: For each Part, count how many Runs contained that Part (that is the number of unique runs containing at least of that Part.) I should end up with a new list similar ...

Counting with 2 criteria
I need to be able to pull counts from a frequently updated list based on associate's name and status of order. -- Amethyst =SUMPRODUCT(--(A1:A100="associate name"),--(B1:B100="status")) should do it for you, adjust the columns/rows to match your sheet. You could have a couple of cells to enter associate name/status into to make things easier; as associate name in C1 and status in D1: =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1)) "Amethyst" wrote: > I need to be able to pull counts from a frequently updated list based on > associate&#...

Count partial matched text string
I have 3 spreadsheets. The first, “Supplies Requests Received” where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet “Supplies Delivered” where Column A is a link of Column B from the above “Supplies Requests Received spreadsheet and Column G (of Supplies Delivered”) contains the names of ALL ...

Assigning numbers to spreadsheet entries...
OK let me explain... I have an excel file with 13,000 cancer cases. Each case (row) is identified by an individual patient's unique ID number and the year/month/day of their diagnosis. Each patient is listed in the database at least twice (at least 2 cancer diagnoses). Right now I have the data sorted by patient ID, then by date of diagnosis. I need to number the cases (in a new column) 1, 2, 3, etc. in the order they present for each individual patient. Example: Patient ID year_diagnosis NEW COLUMN 0001 1981 1 0001 1988 ...

How to transfer a journal entries from excel to GP
I read about integration assistant for excel , How can I transfer a journal entry from excel?.\I have Integration manager already . Thank you You can do it through integration manger or through a macro "Gustavo Rojas" wrote: > I read about integration assistant for excel , How can I transfer a journal > entry from excel?.\I have Integration manager already . > Thank you Integration Assistant for Excel does not work for entering information in scroll windows. We tried it with a client since the promotional material said it can be used with "any" window. ...