Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Find total number of unique model numbers

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Find total number of unique model numbers

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



"Brian" wrote:

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find total number of unique model numbers

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)

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Find total number of unique model numbers

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?

"Brian" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find total number of unique model numbers

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)



--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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?

"Brian" wrote in message
...
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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Find total number of unique model numbers

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" wrote in message
...
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)



--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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?

"Brian" wrote in message
...
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








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find total number of unique model numbers

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

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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" wrote in message
...
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)



--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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?

"Brian" wrote in message
...
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










  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Find total number of unique model numbers

Where do I do that?

"T. Valko" wrote in message
...
Try temporarily setting calculation to manual. After the text numbers are
converted to numeric numbers reset calculation to automatic.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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" wrote in message
...
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)


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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?

"Brian" wrote in message
...
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












  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Find total number of unique model numbers

Goto the menu ToolsOptionsCalculation tabManualOK


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Where do I do that?

"T. Valko" wrote in message
...
Try temporarily setting calculation to manual. After the text numbers are
converted to numeric numbers reset calculation to automatic.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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" wrote in message
...
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)


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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?

"Brian" wrote in message
...
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














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find number of unique entries within a date range Gayla Excel Worksheet Functions 2 April 27th 07 02:58 AM
Find number of unique entries within a date range Gayla Excel Worksheet Functions 1 April 25th 07 11:42 PM
find Unique number Hitesh Pandya Excel Discussion (Misc queries) 1 November 23rd 06 05:13 AM
find numbers in a list which sum a total LINDA Excel Worksheet Functions 1 September 22nd 06 02:06 PM
How do I find the total number of the same number/letter in a row Anne-Marie Excel Discussion (Misc queries) 9 October 21st 05 10:22 AM


All times are GMT +1. The time now is 12:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"