Copy data from one worksheet to another

  • Thread starter Thread starter Ellis Yu
  • Start date Start date
E

Ellis Yu

Dear All,

I've a worksheet contains records belong to "complete" and
"incomplete" status. And I need to seperate these records manually into two
seperated worksheets "complete" and "incomplete". I just wonder is there any
formula to show only records which are "complete" status in a worksheet and
"incomplete" in another worksheet ? It's quite clumsy to do the sorting and
cut & paste to another worksheet everytime. Any idea? Thanks

Best Rdgs
Ellis
 
Perhaps you'd like to try this formulas approach ..

In Sheet1
-------------
Assume the sample source table below is in cols A to D
data from row2 down, with the key column "Status" in col D, viz:

SN Name Desc Status
01 Name1 Data1 Incomplete
02 Name2 Data2 Complete
03 Name3 Data3 Incomplete
04 Name4 Data4 Complete
etc

Using 2 empty cols to the right, say cols F and G,
list in F1:G1, the 2 statuses: Complete, Incomplete

Put in F2: =IF($D2="","",IF($D2=F$1,ROW(),""))

Copy F2 across to G2, then fill down by the max expected number of rows of
data in cols A to D, say down to G200?

In sheet named: Complete
------------------------------­-------
Let's reserve cell A1 to pull in the sheetname

Put in A1: =MID(CELL("filename",A1),FIND(­"]",CELL("filename",A1))+1,32)

(This'll extract the sheetname into A1.
But you need to save the file first.)

Paste the same col headers from Sheet1 into A2:C2, viz.:
SN, Name, Desc

Put in A3:

=IF(ISERROR(SMALL(OFFSET(Sheet1!$E:$E,,MATCH($A$1,Sheet1!$F$1:$G$1,0)),ROWS(
$A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(OFFSET(Sheet1!$E:$E,,MATCH($A$1,S
heet1!$F$1:$G$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1!$E:$E,,MATCH($A$1,Sheet1!$F
$1:$G$1,0)),0)))

(normal ENTER will do)

Note: You'd need to correct/restore the inadvertent line wraps
/ line breaks when you copy > paste the above formula into A3

Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1, i.e. down to C201

You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the status: Complete, i.e. for the sample data-set above,
it'll appear as:

SN Name Desc
02 Name2 Data2
04 Name4 Data4
(rest are blank [""] rows)

Now just duplicate/make a copy of the sheet: Complete,
rename it as: Incomplete
and you'll get the "filtered" rows for Incomplete, viz.:

SN Name Desc
01 Name1 Data1
03 Name3 Data3
(rest are blank [""] rows)

Adapt / extend to suit ..
 
It works perfectly !! I never think about that there're many different ways
to do this, especially using formula. Many thanks to Ron and Max
 
Back
Top