ExcelBanter - Create list from criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Create list from criteria (https://www.excelbanter.com/excel-discussion-misc-queries/200548-create-list-criteria.html)

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.

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.

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.

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.

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.

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.