Extract data from one worksheet
In another column add
=IF(NOT(ISERROR(SMALL(IF($A$1:$A$40="USA",ROW($1:$ 40)),ROW(1:1))-MIN(ROW($A$
1:$A$40)))),
INDEX(A$1:A$40,SMALL(IF($A$1:$A$40="USA",
ROW($1:$40)),ROW(1:1))-MIN(ROW($A$1:$A$40))+1,1),"")
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Copy this formula acroos to the next column, and down as far as you need.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"SKY" wrote in message
...
I would like to extract matched data from one worksheet and put into
another.
For example, a table contains two columns, country and city,
Country City
CN SZ
CN BJ
USA SEA
USA SFO
On a blank worksheet, enter "USA" in A1, how can I copy only the last two
rows?
I try INDEX & MATCH functions, it always finds the first row. How can I
tell
the Excel to make a on-going MATCH until it finish an array?
|