Count Duplicate Alerts

[rrstudio2]

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

The spreadsheets I am working with have about 30,000-50,000 rows, so
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

 

[Dave]

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))

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

Regards - Dave.

 

[rrstudio2]

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

 

[Dave]

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.