Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Fetching data and deleting original data
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fetching multiple fields data | Excel Discussion (Misc queries) | |||
Incremental number for a fetching data | Excel Worksheet Functions | |||
Fetching data from another worksheet | New Users to Excel | |||
Fetching data from other sheets | Excel Discussion (Misc queries) | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) |