Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find number of unique entries within a date range | Excel Worksheet Functions | |||
Find number of unique entries within a date range | Excel Worksheet Functions | |||
find Unique number | Excel Discussion (Misc queries) | |||
find numbers in a list which sum a total | Excel Worksheet Functions | |||
How do I find the total number of the same number/letter in a row | Excel Discussion (Misc queries) |