Copy data from one worksheet to another

  • Thread starter Thread starter DCB
  • Start date Start date
D

DCB

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?
 
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,SMALL($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
 
Back
Top