| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create list from criteria
Morning from a Sunny RSA,
I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create list from criteria
=IF(ISERROR(SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$A$9) ,""),ROW($A1))),"",
INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$ A$9),""),ROW($A1)))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sunnyskies" wrote in message ... Morning from a Sunny RSA, I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create list from criteria
Morning Bob,
Thanks for the quick response, but do not want to use array's Sunnyskies "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$A$9) ,""),ROW($A1))),"", INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$ A$9),""),ROW($A1)))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sunnyskies" wrote in message ... Morning from a Sunny RSA, I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create list from criteria
Then do it your way.
What is wrong with arrays? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sunnyskies" wrote in message ... Morning Bob, Thanks for the quick response, but do not want to use array's Sunnyskies "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$A$9) ,""),ROW($A1))),"", INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$ A$9),""),ROW($A1)))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sunnyskies" wrote in message ... Morning from a Sunny RSA, I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create list from criteria
The end users are not computer literate.
"Bob Phillips" wrote: Then do it your way. What is wrong with arrays? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sunnyskies" wrote in message ... Morning Bob, Thanks for the quick response, but do not want to use array's Sunnyskies "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$A$9) ,""),ROW($A1))),"", INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$ A$9),""),ROW($A1)))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sunnyskies" wrote in message ... Morning from a Sunny RSA, I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create list from criteria
So do it for them, lock the sheet down and don't let them change it.
-- __________________________________ HTH Bob "Sunnyskies" wrote in message ... The end users are not computer literate. "Bob Phillips" wrote: Then do it your way. What is wrong with arrays? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sunnyskies" wrote in message ... Morning Bob, Thanks for the quick response, but do not want to use array's Sunnyskies "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$A$9) ,""),ROW($A1))),"", INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=A$10,ROW($A$1:$ A$9),""),ROW($A1)))) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sunnyskies" wrote in message ... Morning from a Sunny RSA, I have a list: Column A Column B Unit # Unit Type 98R01 Rigid 97D02 Drawbar 00R03 Rigid 02S02 Semi Now in a cell A10 I have got the word Rigid, and in cell A11 down I want to list all the unit ids that match the word Rigid as in column B. The in column B10 the word Drawbar with the formula in B11 to pull that unit #. The same for the unit type Semi. Answer should be: Rigid Drawbar Semi 98R01 97D02 02S02 00R03 Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
| Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
| Can you create a LIST and DATA VALIDATION CRITERIA in same cell? | Excel Discussion (Misc queries) | |||
| Create list of text matching criteria | Excel Worksheet Functions | |||
| Create a list based on single shared criteria | Excel Worksheet Functions | |||
Linear Mode
