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
1114 Views

Similar Articles

[PageSpeed] 56

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:

sort that will only contain conditional formatted cells
I've now been able to finish the pivot report I've been working on highlighting drop in revenues after 3 consecutive months, with much help from many of you and now have been asked to sort this report so that only the rows that contain the conditional formatting that reflects these drops show up for the CEO's use. If anyone can give me advice on how to do a sort to only reflect these rows it will be appreciated. check these links: http://www.excelforum.com/showthread.php?threadid=214629&highlight=sort+by+colored+cells http://cpearson.com/excel/colors.htm i have read ...

only allow data entry to 2nd decimal
When entering sales data in an exel spreadsheet, some extra numbers are getting keyed in mistake. the spreadsheet is formatted in accounting, 2 decimal places. the correct figure is $24.13. However sometimes this is keyed in $24.133 by mistake, and the user doesn't know, because the number is still formatted $24.13. Is therre a way to get the user to enter data with no more than 2 decimal places? Select all the cells that are affected then select from the menu: Data>Validation>Select tab Settings. Allow:Custom, Formula: =ROUND(A1,2)=ROUND(A1,3) Note that A1 is the first cell ...

defining unique range of cells for different sheets as the same n.
I want to use the same name, month9, to refer the same range of cells, but on different sheets, how do i turn off the global define? When you define the name (Insert|name|Define), include the sheet name in the "names in workbook" box: sheet1!month9 or 'sheet 1'!month9 You can select your range and type this in the name box (to the left of the formula bar), too. KSAPP wrote: > > I want to use the same name, month9, to refer the same range of cells, but on > different sheets, how do i turn off the global define? -- Dave Peterson ...

Display results based on a date range
Hello, I’m trying to creating a form that will display info from my table called “tblCalls”. I would like the info to be display between to a “Date” range entered by the user. Within my “tblCalls” there is a field that contains the date of each record. In my form called “frmCallLog” are the two textboxes, in which the user will enter the desired date ranges “Calls from [textbox “8/20/06”] through to [textbox “8/20/07”].” The form will then auto populate with the results. There are many textboxes that will at that time auto populate with results, such as, how many times does “Jason”, “Bob”...

Beginner: Is HWND unique?
Hi! When I start an application twice and ask each application for the handle of its main window (calling an inbuild COM function), are the returned dwords guaranteed to be different? Thanks a lot for your help! Volker -- For email replies, please substitute the obvious. It is not guaranteed to be the same or different. You might get the same HWND towice in a row, or might get different ones between two instances of your program. AliR. "Volker Hetzer" <firstname.lastname@ieee.org> wrote in message news:ed9gei$di9$1@nntp.fujitsu-siemens.com... > Hi! > When I start an ...

How can I define unique font per each Outlook profile?
We have several Outlook profiles on one computer and each user wants their own font. I noticed changing to one type of font in one profile affects all the other user profiles in Outlook. Is there a way to assign unique fonts to each Outlook profile? Many thanks experts... Each user would need their own Windows login.=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 http://www.outlookcode.com/jumpstar...

unique record numbers
I am trying to create a unique PIN for members in a table (not the primary key) that will be 4 digits long, and would like each new entry to be incremental. I am very new, and would appreciate a detailed instruction (though I can get around access fair enough). Any help would be appreciated. On Fri, 5 Mar 2010 19:19:44 -0800, Rev David Bissas <Rev David Bissas@discussions.microsoft.com> wrote: The "DMax + 1" technique should work for you. Google or Bing for it. -Tom. Microsoft Access MVP >I am trying to create a unique PIN for members in a table (not t...

uploading excel journal entry into Great Plains
At work, we create journal entries in excel. We then manually enter the exact same data in Great Plains to officially post to the books. I think it's redundant and a bloody waste of time. Is it possible to create a journal entry in excel that I can directly upload into Great Plains? I would greatly appreciate it if you could assist me with this...or point in me the right direction on how to go about it. Thank you so much Patrocinia Ilangilang ...

Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell multiple items, but I only need to show the vendor once so I can pull other information. There are also may be blanks in the rows if the vendor is inactive. Thanks! Let me add one more twist to this problem: The unique values also need to filter out where it may be 'VendorA - West' and 'VendorA - East' and combine them into one record. I only need it to show VendorA and not both records East and West (same company, just different drop ship location). Thanks! "Karl Burrows" <kfb1@spa...

Journal Entry posted Twice
This is a multi-part message in MIME format. ------=_NextPart_000_0040_01C87CA8.977AB350 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I experienced an episode, entering a Journal entry into GP, I somehow = observed entry being duplicated (entered the same entry twice); didn't = key it twice). At the same time, the same thing happened with another user the same = day. When the other user went to post, GP gave his a SQL error message, = didn't give a screen to print the journal edit, and it appeared that he = would need to hit...

Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList? I know there is an algorithm function "unique" performing on containers in Standard C++ library. Is there a similar function in MFC? What am I doing now to avoid adding same strings to container is: CStringList strList; ..... if (strList.Find("somestring") == NULL) strList.AddTail("somestring"); But I realized it may be an inefficient way. Any good sugestion? I would have used a CMap for this. Then, when all insertions were done, I might consider converting it to a CArray...

Exchange based AD rights?
Did not see quite where this might fit in the security area so will post here. This is the deal. We have one of those domains that is just so locked down that many common operations such as editing user attributes or SMTP address cannot be done when a user account is moved outside our particular OU. We have an OU where our division has rights and we can do what we need. Now what goes on is users are moved out of our OU yet still have a mailbox on our Exchange 2007 server and many times I need to make changes yet am unable to. Is there a way to basically set a secuirty policy...

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? http://excel.tips.net/Pages/T002980_Conditional_Formats_that_Distinguish_Blanks_and_Zeroes.html This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Need Help on COM AddIn registry entries
Hi, I have 2 COM AddIns for the "TenderEnd" Hook. But when I run the POS only the AddIn that is added first into the registry is getting invoked, and when I check the registry again after exiting the POS, the second registry entry vanishes... How do I have 2 AddIns for the TenderEnd Hook... I have registered both the AddIns (the DLLs) and they both work perfectly invidually... Here are the registry entries that I added.... Hook Number: 000 Caption: Hook1 Description: Hook1 HookType: 15 ObjectName: AddIn1.class1 Parameter: 1 Hook Number: 001 Caption: Hook2 Description: Hook2 HookTy...

EXCEL Mask for data entry
I don't have MS Access but I want to create an Access type Entry Mask for the entry of data into appropriate cells within a background work sheet. I want to create this as a simple entry form for non Excel users. The form would have 6 column and there would be a need for a look up table so when I entered an equipment asset number, its appropriate serial number would auto insert? Am I being too hopeful that such a facility exists within Excel? If by chance it does, where would I look for instructions? Hopefully Bill This is very possible in Excel but there are a few things to unde...

New entries are not permanent
My wife and I each have Microsoft Money 2001(Mine is on a PC with Windows Millenium, hers is on a laptop with Windows 2000). Mine works fine, but hers does not keep the current entries. Instead each time Money is opened, we must restore from the backup file to bring it up to date. I suggest you figure out where the file is really stored and what's stepping on it. Money uses a database similar to Access. Changes are written to disk immediately. Something else must be going on here. "Richard Shaw" <anonymous@discussions.microsoft.com> wrote in message news:1933001c44cd4...

m_pDocument Doc/View unique id for each view
I'm using the standard mfc mainform with a doc/view. While I'm in the view portion I need to track the actions taken individually and separately for each view. So when when I click on view one I track that in a separate place than view two. I there a unique id or index for each view? Something like m_pDocument->ID? Thank you, Joshua <jtfaulk@eudoramail.com> wrote in message news:1111085780.370621.117620@f14g2000cwb.googlegroups.com... > I'm using the standard mfc mainform with a doc/view. While I'm in the > view portion I need to track the actions taken...

Unique Values, not Unique Records
My data looks like the following: ID Type Amt 1234 A $10.00 1234 B $10.00 1234 C $10.00 I would like to find a way to get only one of the items on to a new spreadsheet, because they are duplicate records in my eyes. The Advanced filter does not work because each record is unique. Can anyone help? in the dialog for the advanced filter, make sure only the ID column is referenced "steph44haf" wrote: > My data looks like the following: > > ID Type Amt > 1234 ...

PLEASE HELP! need conditional formatting related to date, blank cells and today's date
I want to create a conditional formmatting formula that will highlight a cell if that cell is blank and another cell that has a date in it is more than 30 days from today's date. So, if J2 has a date in it and that date is greater than or equal to 30 days past today's date AND K2 is blank, I want to highlight K2 red. If K3 is not blank I dont want it to be highlightd. can anyone help me with the correct formula/conditional formatting? Thanks so much! Try this in the Formula box of the CF dialogue: =3DAND(K2=3D"",J2-TODAY()>=3D30) If J2 is in the past (not clear from...

Delete based on another table
I can SELECT but I cannot DELETE as follows. DELETE Transcript.*, Transcript.SEM FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY WHERE (((Transcript.SEM)="3")); "Song Su" <csitnnn@hotmail.com> wrote in message news:OI0xTTSYIHA.5472@TK2MSFTNGP06.phx.gbl... >I can SELECT but I cannot DELETE as follows. > > DELETE Transcript.*, Transcript.SEM > FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY > WHERE (((Transcript.SEM)="3")); > > In the query design right-click and bring up the query's propert...

How can SUM a column based on values of other columns?
I need help Suming up Amount column based on values of three different columns. Let's say I have the following Detail Data: Month Institute Account Type Amount Memo 1/1/2008 Bank of America Checking 500 Deposit 1/1/2008 Bank of America Checking -200 Check #111 1/1/2008 Bank of America Saving 400 Deposit 1/1/2008 Bank of America Saving 150 Deposit 1/1/2008 CitiBank Checking 50 Deposit 1/1/2008 CitiBank Saving 100 Deposit How can I SUM t...

Can I enable forms based auth on OWA if ISA is already enabled ?
Users can access webmail from internet and get to ISA 2004->OWA. ISA 2004 is configured for forms based. Internal users get access to webmail directly to OWA box. In this circumstance, if internal users need to get access to forms based authentication, can I just enabled the forms based on the OWA box ? Is that going to conflict when people are trying to hit ISA ->OWA from the Internet ? ...

Multiple conditional formulas
HELP! I have a pricing model in which I have 4 pull down lists in which the user can select multiple combinations of criteria: Pulldown list #1 Country State City Zip Pulldown list #2 Yes No Pulldown list #3 Hosted GDS Data File Pulldown list #4 Under $1M $1-$5M Over $5M I currently have pulldown lists #1 & #2 in the condition below but now need to add pulldown lists #3 & #4 to the formula below without going over the 7 "if" limit. The catch is there is a discount % that needs to be added on to the existing criteria from the vloo...

Unique Constraints
Hello, I have a Users table with 2 fields, UserName and Visible. The same UserName can exist many times with Visible = 0 but just one record can exist for the same UserName with visible = 1. In my Stored Procedure before inserting a new visible user I check the user doesn't exist in the table with visible = 1, but that is not enough, there are cases when the Stored Procedure is called in the same moment with the same parameters and there is duplicated data. I can't define a Unique constraint because UserName with visible = 0 is not unique, what would be the best way...

Calendar Entries
It seems as if my calendar entries disapear after 6 months. I was not familiar with the archive feature so i would say "No" when asked to auto archive. Is there a way to keep 2-3 years worth of info just in the information store? Chad Graves ...