Extract data from one worksheet
Same formula, just copy it across as well as down.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"SKY" wrote in message
...
Hi, Bob,
Thanks for your help, I copy the formula and it is working. Please excuse
me
as a beginner but yet trying to get the job done.
Can you show me the next formula to copy the corresponding cell to the
next
column, that means, have both country and city as a result,
USA SEA
USA SFO
Yours,
SKY
"Bob Phillips" wrote:
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?
|