How to select item without using advanced filter?
 

Does anyone have any suggestions on how to select item based on following
conditions?
Under column F, there is a list of numbers.
Under column G, there is a list of types.
For example
1 Car
2 Orange
3 Car
4 Water
5 Orange
6 Car

I would like to select all the number with the type - Car under column H.
I try following code without luck
=INDEX(F:F,MATCH("Car",G:G,0)), I would like to select the
Small(number,row()) based on rows. Such as
In cell H1, it should return 1
In cell H2, it should return 3
In cell H3, it should return 6

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to select item without using advanced filter?
 

Try this array formula** :

Assuming you enter this formula in cell A1...

=IF(ROWS(A$1:A1)<=COUNTIF(G$1:G$6,"car"),SMALL(IF( G$1:G$6="car",F$1:F$6),ROWS(A$1:A1)),"")

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


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to select item based on following
conditions?
Under column F, there is a list of numbers.
Under column G, there is a list of types.
For example
1 Car
2 Orange
3 Car
4 Water
5 Orange
6 Car

I would like to select all the number with the type - Car under column H.
I try following code without luck
=INDEX(F:F,MATCH("Car",G:G,0)), I would like to select the
Small(number,row()) based on rows. Such as
In cell H1, it should return 1
In cell H2, it should return 3
In cell H3, it should return 6

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to select item without using advanced filter?
 

I'm having one of those days where nothing I do is right!

I forgot to say:

Copy the formula down until you get blanks.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

Assuming you enter this formula in cell A1...

=IF(ROWS(A$1:A1)<=COUNTIF(G$1:G$6,"car"),SMALL(IF( G$1:G$6="car",F$1:F$6),ROWS(A$1:A1)),"")

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


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to select item based on following
conditions?
Under column F, there is a list of numbers.
Under column G, there is a list of types.
For example
1 Car
2 Orange
3 Car
4 Water
5 Orange
6 Car

I would like to select all the number with the type - Car under column H.
I try following code without luck
=INDEX(F:F,MATCH("Car",G:G,0)), I would like to select the
Small(number,row()) based on rows. Such as
In cell H1, it should return 1
In cell H2, it should return 3
In cell H3, it should return 6

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to select item without using advanced filter?
 

An alternative, non-array set-up
Data assumed in cols F and G, from row 2 down
You can input the selection fro col G in H1, eg in H1: Car

In H2:
=IF(H$1="","",IF(G2=H$1,ROW(),""))

In I2:
=IF(ROWS($1:1)COUNT(H:H),"",INDEX(F:F,SMALL(H:H,R OWS($1:1))))
Select H2:I2, copy down as far as required to cover the max expected extent
of data. Col I will return the required results from col F for the selection
entered in H1, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
Does anyone have any suggestions on how to select item based on following
conditions?
Under column F, there is a list of numbers.
Under column G, there is a list of types.
For example
1 Car
2 Orange
3 Car
4 Water
5 Orange
6 Car

I would like to select all the number with the type - Car under column H.
I try following code without luck
=INDEX(F:F,MATCH("Car",G:G,0)), I would like to select the
Small(number,row()) based on rows. Such as
In cell H1, it should return 1
In cell H2, it should return 3
In cell H3, it should return 6

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric