Copy data from one worksheet to another

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?
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Excel Moving data to sheet2 from sheet1 0
copy worksheet 2
VBA group the number in the Column and Copy to the new worksheet. 0
Excel Help to copy information from one row at a time to another sheet 1
Copying Formulas from One Worksheet to Another -- Very Easy Questi 3
Auto copy data rows between sheets depending on date entry 4

Top