```I have a spreadsheet that in a simplified form looks like

Drug   Pump
A       1
C       4
A       1
B       3
A       1
A       2
B       3
C       1
C       5

I need to determine how many different pumps each drug was on.  So I
need to return

Drug  #Pumps
A       2
B       1
C       3

that may rule out array formulas.  I would prefer to not use pivot
tables because the entire table is going into a pivot table for
analysis.  If needed, I can resort the table but would prefer not to.
The best layout for my needs would be to have the # pumps column added
onto the original data table like

Drug   Pump  #pumps
A       1          2
C       4          3
A       1          2
B       3          1
A       1          2
A       2          2
B       3          1
C       1          3
C       5          3

Any thoughts on how I could do this with a formula?  I can also use
visual basic because this spreadsheet has a macro that does some pre-
processing on it.  It seems like this should be easy but I haven't run
into anyone who can do it yet.

Thanks,
Andrew V. Romero
```
 0
rrstudio2 (8)
6/19/2008 6:44:08 PM
excel 39879 articles. 2 followers.

3 Replies
439 Views

Similar Articles

[PageSpeed] 25

```Hi,
It's a bit messy, but I think it can be done with 2 helper columns.
Only tested with your sample data.
With drugs in ColumnA and Pumps in ColumnB, and data start Row2:

In C2 enter:  =A2&B2
In D2 enter:  =1/COUNTIF(\$C\$2:\$C\$50000,C2)
In E2 enter:  =SUMPRODUCT(--(\$A\$2:\$A\$50000=A2)*(\$D\$2:\$D\$50000))

Copy C:E down to the end of your data.
Answers you want are in ColumnE

I believe if you have XL2007, you can use whole column refs ie
In C2 enter:  =A2&B2
In D2 enter:  =1/COUNTIF(C:C,C2)
In E2 enter:  =SUMPRODUCT(--(A:A=A2)*(D:D))

Perhaps one of the boffins can condense it into 1 column, but not me.
At least there are no array formulas.

Regards - Dave.

```
 0
dave871 (680)
6/19/2008 11:13:00 PM
```Hi Dave,
Thanks for the idea.  Unfortunately, starting to use countif with
50000 rows takes too long (5+ minutes before I cancelled it).  Any
other ideas?  I came up with a workaround yesterday that is pretty
messy and uses about 4 helper columns, but would like something
simplier.

Thanks,
Andrew V. Romero
```
 0
rrstudio2 (8)
6/20/2008 4:10:44 PM
```Hi Andrew,
The only other idea I have at the moment is to take a longer coffee break
(ie leave it running while you do something else - like watch a movie.
Problem is it has to check 50000 rows 50000 times!

A macro may run quicker. Wanna try that? Can't look at it right now, but
maybe later tonight.

Regards - Dave.
```
 0
dave871 (680)
6/20/2008 6:11:00 PM

Similar Artilces:

Negative Number when Counting Yes/No
I have a table that has a lot of number fields and some yes/no fields. I am doing a query to get averages for the number fields and a count for all the yes fields. But those fields come up with the right number except they are negative. There are 32 records in the database and one of the yes/no fields have 20 yes, but the query gives me -20. How can I change it so that it is a positive number? -- Robyn ABS(Sum(Fieldname)) would be faster. On Mar 20, 4:10 pm, Dale Fye <dale....@nospam.com> wrote: > Robyn, > > Access stores "Yes" responses as True (has a nume...

Hello In a worksheet, I have rows of numerous students and a column with their achieved grade and a column with their target grade per subject. So each subject has 2 columns and there are around 14 subjects. I would like to add a 3rd column for each subject with a formula that would calculate the difference. But the grades are A, B, C etc to F and not numbers. Can anyone help me with this please. ie Name ART TARGET ART DIFF BUS STU TARGET BU ST DIFF JO A B -1 B A +1 DIFF being the c...

Essential Account view/duplicate accounts??
Just upgraded to Money 2005 (from 04) and I now have four credit card accounts instead of two. After calling MS tech support I was told that the reason I have four accounts instead of two is; two have the new Essential Account Register view and two have the Advanced Account Register view. This is dumb. Why does it show you the different views as duplicate accounts? It makes it look like you have more accounts then you really do. It even calls them a different name of the original accounts... Anyone experience this strange function??? Very disappointed as this feature doesn't...

Creating Duplicate Info
I have five cells of info that need to be added to over 2000 other sku's. Example I have A, B, C, D, E and 1 - 2000 I need it to be 1A, 1B, 1C, 1D, 1E, 2A, 2B etc. I was wondering is there a faster way instead of insterting 5 lines 2000 times? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201003/1 You can do it easily with code. Sub AAA() Dim Letters(1 To 5) As String Dim LNdx As Long Dim N As Long Dim RowNum As Long Letters(1) = "A" Letters(2) = "B" Letters(3) = "C" ...

Duplicate value
I have a form with a subform on it. The subform has 5 fields, set up as datasheet view: ID (autonumber) Start End Qty (sum of end-start) Line Type (combo box) I've been asked to see if the 'End' can automatically be entered at the 'Start' on the next line; however, there is upon occassion the need to change the start number. For example: ID Start End Qty Line 1 0 215 215 (whatever) 1 215 300 85 (whatever) 1 325 ...

I using Outlook 2003 and access mail acount on Gmail IMAP and desktop alert does not work, although i getting tray icon, but alert message does not pops up. I dont believe alerts work in imap folders "John" <Johnisbest@gmail.com> wrote in message news:OcAfSNZYKHA.2816@TK2MSFTNGP04.phx.gbl... >I using Outlook 2003 and access mail acount on Gmail IMAP and desktop alert >does not work, although i getting tray icon, but alert message does not >pops up. > Is there plugin to enable them to show on IMAP? "DL" <notvalid@spoofaddress.co.uk> wro...

Counting Days
Hello - I'm trying to figure out a formula that tells me the number of days until a date or tells me how many days have passed since the date. Meaning, if today is 01/01/10 and my target date is 01/05/10, I would like the formula to return "-5". If today is 01/10/10 and my target date was 01/05/10, I would like the formula to return "5". Any help would greatly be appreciated. Thanks for your time. DyingIsis, You can substract dates from each other. Each day = 1. So if you have a date in A1 and a date in A2 you can subtract the two. Just...

Hello: Two questions. First, generally speaking, how do you configure SQL 2000 to e-mail end users? I have a SQL script that shows, in an accounting database, inventory items that have not been purchased from a vendor within the last year. I want to e-mail that list of items to end users on a monthly basis. That list of items amounts to a report. Secondly, my script has a specific date. It, basically, says < 06/01/06. I don't want the IT staff to have to come in and update the date parameter. Is there a way to place syntax in the script to pull the date from the operati...

Counting Cells
I wish to count the cells in one column using criteria in another column. The criteria is in Text form. Any help Please!!! :cool: -- markinblackuk ------------------------------------------------------------------------ markinblackuk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28360 View this thread: http://www.excelforum.com/showthread.php?threadid=479409 Hi Try =COUNTIF(A:A,"="&B1) Regards Roger Govier markinblackuk wrote: > I wish to count the cells in one column using criteria in another > column. The criteria is in Text ...

How to you count cells that contain any entry?
=IF(J9="x",1,IF(K9="x",1.5,IF(L9="x",2,IF(M9="x",2.5,IF(N9="x",3,IF(O9="x",3.5,IF(P9="x",4,""))))))) This works fine for me if my customers use an x, but some use a tick or a star, or any computer letter/symbol. Can this equation be made to work irrespective of what the entry in the cell is? =IF(J9<>"", 1, etc. -- HTH RP (remove nothere from the email address if mailing direct) "Robin C" <Robin C@discussions.microsoft.com> wrote in message news:B83FB0C1-18BC-420C-A951-C9809...

Count Textboxes and other objects
Is there any way that you can count the number of textboxes and other drawing objects in a sheet using c# or VB from an application and change their contents? Dana: this code should work Sub count_boxes() Count = 0 For Each myshape In ActiveSheet.Shapes If myshape.Type = msoTextBox Then Count = Count + 1 End If Next myshape End Sub "Dana" wrote: > Is there any way that you can count the number of textboxes and other > drawing objects in a sheet using c# or VB from an application and change > their contents? > > > > > > ...

Restart Record Count
Hello, I am counting records in a query and I need the count to restart at 1 for each sub-set. In the example below, I want to count [Action ID] within each set of [ISSUE ID] and result in [New Count]. Any suggestions are very much appreciated. ISSUE ID Action ID New Count 123 95 1 123 97 2 123 98 3 123 101 4 456 110 1 456 116 2 456 118 3 Thanks so much!! -- ~ Chris the general query (for display purposes) is SELECT (SELECT COUNT(*)+1 FROM P P1 WHERE P1.ID < P.ID) AS ROWNUM, P.* FROM P P; so you'd want SELECT (SELECT COUNT(*)+1 FROM yourtablename P1 WHERE P1.ISSUEID = P.ISSUEID ...

Hi All. Just a quick question We are setting up mail restrictions within our company, When the users mailbox exceeds the max size the box is closed. Easy. However, the user continues to receive email alerts every 15 minutes untill the mailbox is reduced to a value lower than the mail limit size. Is it possible to change the frequency of the emails to 1 email per day. instead of 92 in 24 hours. As most our employees are away from the office alot. Regards, Alan you can schedule these warnings on the Storage Warnings tab in the properties of the Information Store Site Configuration o...

Remove Duplicate
I used OCR software to read the Names and addresses off ~500k images. For each image we received ID and Account Number combinations that correspond to a Name and Address. Within the data set there are several examples of each ID, Account, Name and Address combination. We are interested in removing the duplicates and only having the distinct records. The software is not 100% accurate so the Names and Addresses can be different from result to result for the same ID/Account. My thought was to use a majority voting approach. Below is a link to an example of the results. ...

User has two issues: Notices that her boss' tasks (she has delegate rights) show up multiple times in Task View. Same thing with Contacts. Has anyone seen this happen before? ...

How to tell COUNTIF to not count a few characters
Im working on a colum that is a medical schedule for Doctor who will be working that day so that I know how many exam rooms will be needed - right now Im working with: =COUNTIF(F14:F43,"*") which tells me how many cells are not blank. But I would like to have countif not count cells in that same colum who have values like: OFF, ADMIN, OFF SITE and etc. Am I using the correct function? PLEASE HELP! Hi Rafa Countif is a very efficient function =COUNTIF(F14:F43,"OFF*") will deal with both Off and Off Site =COUNTIF(F14:F43,"Admin") Deduct these va...

Hello is there anyway to get the desktop alert for new mails for all folders including the default folder 'Inbox' ? Using Outlook 2003 See http://www.howto-outlook.com/howto/mailalert2003.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Properly back-up and restore your Outlook data -Creating a Permanent New Mail Desktop Alert in Outlook 2003 ----- "Vik" <anonymous@anonymous.com> wrote in message news:uLkpNgLQFHA.3156@TK2MSFTNGP15.phx.gbl... > Hello is there anyway to get the desktop alert for new mails for all > folders in...

counting checkboxes
hello, i have an excel sheet on which there are several checkboxes (form) in different areas, divided into sections, of the sheet. i would like to count the checkboxes that are checked/unchecked without using a macro. that is, using some kind of an inbuilt xl function, such as sum with an if condition. is it possible? thanks, mac. --- Message posted from http://www.ExcelForum.com/ Checkboxes have a Linked Cell property so you could easly have these i a column with a COUNTIF() function to add up TRUE or FALSE -- Message posted from http://www.ExcelForum.com Mac, You need VBA for th...

Need sum in pivot table, not count
I have a spreadsheet which I'm trying to show the sum of each item, but the pivot table wizard only gives me a count. It will do a sum for each column or row, but not each item. I can do this in a snap if I import the spreadsheet into access and run a crosstab query, but the folks that are going to be using this cant use access. here's a sample of my data: Item Amount Month A02214 0 JANUARY A056480015 0 JANUARY A13 -348 JANUARY A16EPG-AD -2000 JANUARY A18 -1950 JANUARY A14RS ...

This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C7C31D.0FA359E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, We're currently on Great Plains 8.0 and we have come across an issue = regarding menus. We're getting duplicates when switching from one = company to another. Example: When logged on to company A. Click on Tools on the Menu bar and we see a = drop down menu containing the following Setup Utilities Routines Customize Integrate Macro Resource Description Then we switch to another company...