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,SM ALL($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,SM ALL($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

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" wrote:
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?

Hey Max,

Thank you again for your help. Really appreciate it.

Regards,
Prem

"Max" wrote:

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" wrote:
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?

Welcome, Prem
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"prem" wrote in message
...
Hey Max,

Thank you again for your help. Really appreciate it.

Regards,
Prem