Find total number of unique model numbers

[Brian]

Howdy All,

I have a workbook that contains service data for units.

Column A = Model Name (alpha) 87 model names that repeat throughout the
column
Column B = Serial Number (numeric), unique number that repeats throughout
the column
Column C = Service Hours
Column D = Service Req Number

Model Name appears many times in column A
Serial Number appears many times in column b.

I want to determine how many of each Model Number I have with a unique
Serial Number.
But each Model Name and Serial Number can occur many times in their
respective columns if there have been multiple Service Requests.


Thanks,
Brian

 

[igorek]

Brian,
Use Pivot table. It will solve all your issues..

 

[T. Valko]

Not sure I understand but see if this is what you want.

Array entered** :

=COUNT(1/FREQUENCY(IF(A1:A10="Model1",B1:B10),B1:B10))

Will count unique serial *numbers* in column B that correspond to "Model1"
in column A.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Brian]

igorek, thanks, i would use a pivot table, but i don't know how

T. Valko, thank you.
Can i changed that to:

=COUNT(1/FREQUENCY(IF(A2:A7024="Model1",B2:B7024),B2:B7024))

Which represents the range of the data columns, but it gives me 0?

 

[T. Valko]

You have to change "Model1" to whatever the actual model name is!

Also, did you array enter the formula?

Array entered** :
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Brian]

Yes, I did enter the actual model number, and I didn't hit ctrl+shift+enter
for array formula.

I think i know what the issue may be, the serial numbers are stored as text,
and when I tried to convert them to numbers, it takes 5 seconds for every
cell?!?!

 

[T. Valko]

Try temporarily setting calculation to manual. After the text numbers are
converted to numeric numbers reset calculation to automatic.

 

[Brian]

Where do I do that?

Try temporarily setting calculation to manual. After the text numbers are
converted to numeric numbers reset calculation to automatic.

 

[T. Valko]

Goto the menu Tools>Options>Calculation tab>Manual>OK