Hi, I have a worksheet with 5 columns and 600 rows. I need to copy all the
rows into a new worksheet that have a specific value in column 3. What
formula do I use for this?
--
David Banks

Use
datafilterautofilterfilter as desiredcopy/paste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DCB" wrote in message
...
Hi, I have a worksheet with 5 columns and 600 rows. I need to copy all the
rows into a new worksheet that have a specific value in column 3. What
formula do I use for this?
--
David Banks

Here's a formulas set-up to accomplish it ..

Assume source data in Sheet1, cols A to E,
data from row2 down, key col = col C

In another sheet
Assume the specific value for col C will be entered in A2
In B2: =IF(A$2="","",IF(Sheet1!C2=A$2,ROW(),""))
Leave B1 empty

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1))))
Copy C2 across by 5 cols to G2. Select B2:G2, fill down to cover the max
expected extent of source data in Sheet1, say down to G600. Minimize/hide
away col B. Cols C to G will return the required results from Sheet1
(dependent on the input in A2), with all lines neatly packed at the top.

Success? High five it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"DCB" wrote:
Hi, I have a worksheet with 5 columns and 600 rows. I need to copy all the
rows into a new worksheet that have a specific value in column 3. What
formula do I use for this?
--
David Banks