Fetching data and deleting original data

[prem]

Hi guys this is my situation.

I have 2 sheets now, Sheet1 and Sheet2. In both sheets, my headers for the
data are exactly the same. Only thing is, in sheet1, I have an extra column
of data labelled "proessed?".

Lets assume that I have 4 columns in sheet1, "name", "sex", "location" and
the extra "processed?" column.

In sheet2, I have the same first 3 column as sheet1, only without the
"processed?" coulmn.

What I need is a way to fetch the data from sheet1 to the corresponding
column in sheet2 when the "processed?" column contains the word "yes". I
believe I already have created a similar thread in which Max gave a good
reply.

However, now I need the data from sheet1 to be deleted when it is sent over
to sheet2. Is there a way to do this?

I appreciate the help.

Regards,
Prem

P.S the reply given by Max for my previous issue is as follows:

Here's a formulas play to deliver the required results dynamically from
Sheet1 into Sheet2

Source data is in Sheet1 as posted, data from row2 down
with key col = col O (payment mode)

In your Sheet2
Set aside an empty col to the right for the criteria, say col K?
Put in K2: =IF(Sheet1!O2="GIRO",ROW(),"")
Leave K1 empty. Copy K2 down to cover the max expected extent of data in
Sheet1's col O, say, down to K50?

Then to extract "student name" from Sheet1's col A (into col C in Sheet2),
Place this in C2, fill down to C50:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(Sheet1!A:A,SMALL($K:$K,ROWS($1:1))))

Similarly to extract corresponding "block number" & "street name" from
Sheet1's cols C & D (into cols D & E in Sheet2)
Place this in D2:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(Sheet1!C:C,SMALL($K:$K,ROWS($1:1))))
Copy D2 to E2, fill down to E50. All result lines will appear neatly packed
at the top.

P/s: The col to be returned from Sheet1 is defined in this part:
... INDEX(Sheet1!C:C,
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[Max]

Here's an easy dynamic formulas play which would deliver the equivalent
results that you seek on both counts. It might appeal to you.

In Sheet1,
you have the source data in cols A to D as posted,
with the key col = col D ("processed?")

Create 2 adjacent criteria cols to flag the 2 statuses in "processed?",
ie "yes" and <>"yes"

In E2: =IF(D2="yes",ROW(),"")
In F2: =IF(D2<>"yes",ROW(),"")
Copy E2:F2 down to cover the max expected extent of source data,
say down to F200?

Then in Sheet2,
you could extract all the "yes" cases by pointing it to Sheet1's col E

In A2
=IF(ROWS($1:1)>COUNT(Sheet1!$E:$E),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$E:$E,ROWS($1:1))))
Copy A2 to C2, fill down to C200 (same extent)

And in Sheet3,
you could extract the "remainder" cases (except "yes")
by pointing it to Sheet1's col F

In A2
=IF(ROWS($1:1)>COUNT(Sheet1!$F:$F),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROWS($1:1))))
Copy A2 to C2, fill down to C200 (same extent)

Sheet2 & Sheet3 will give you the 2 positions that you seek which is dynamic
to Sheet1's source data as it changes in the key col D.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[prem]

Hey Max,

Thank you again for your help. Really appreciate it.

Regards,
Prem

 

[Max]

Welcome, Prem
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik