Conitional copying of a serie of rows from one spreadsheet to another

  • Thread starter Thread starter david
  • Start date Start date
D

david

I have a large worksheet with data in it. I want to copy only those
rows that meet certain criteria to another worksheet. For instance - If
there is a certain value in column C and a date in column D falls
between a certain range I want to copy the data in the row to another
summary worksheet. I want to ignore the rows where the conditions are
not me so I don't have gaps in the summary.

Help greatfully received


David
 
david said:
I have a large worksheet with data in it. I want to copy only those
rows that meet certain criteria to another worksheet. For instance - If
there is a certain value in column C and a date in column D falls
between a certain range I want to copy the data in the row to another
summary worksheet. I want to ignore the rows where the conditions are
not me so I don't have gaps in the summary.
Click to expand...

One play which makes it dynamic to the source ..

Assume source data in Sheet1, cols A to F (say),
data from row2 to a max expected row100

Assume the required lines to be copied into a new sheet are those where col
C = "x", and where the dates in col D fall within the month of May 2006 (say)

In Sheet2,

Put in A2
=IF(ROW(A1)>COUNT($G:$G),"",INDEX(Sheet1!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0)))
Copy A2 to F2

Put in G2
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2>=--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))
(Leave G1 empty)

Select A2:G2, fill down to G100
Format col D as dates

The above will return the required results from the source sheet, all neatly
bunched at the top. (Hide away the criteria col G)

Adapt to suit ..

---
 
Put in G2:
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2>=--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))
Click to expand...

For greater flexibility, instead of hardcoding the 3 criteria, the above
formula could point to 3 cells, say H1:H3, where H1 will house the value in
col C, and H2:H3 will house the start and end dates of the desired period.

For the above, we could then put instead in G2, and copy down to G100:
=IF(OR($H$1="",$H$2="",$H$3=""),"",IF(Sheet1!C2="","",IF(AND(Sheet1!C2=$H$1,Sheet1!D2>=$H$2,Sheet1!D2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G

---
 
Max

Thanks very much for the help

=IF(ROW(A1)>COUNT($G:$G),"",INDEX(Sheet1!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:­$G,0)))

However, when I put this formula into Excel it give an error at the
penultimate G - is there something I am doing wrong?

David
 
Max

I pressed reply before I meant to!

I am a bit confused as to what thye ($G:$G) section of the formula is
actiually doing - can you tell me please.
david wrote:

David
 
david said:
I am a bit confused as to what the ($G:$G) section of the formula is ..
Click to expand...

Col G is the criteria col which flags lines in the source data satisfying
the required criteria via arb. row numbers. The extract formulas in cols A to
F will read the arb. row numbers returned in col G to produce the required
results in the new sheet. Col G can be hidden away, if desired.

You were probably hit by the inadvertent line break(s) when you tried to
copy teh formula & paste direct from the response into your sheet's cell's
formula bar.

Here's a sample construct for your easy reference:
http://www.savefile.com/files/5883908
Conditional copy of lines fr source to new sheet

Sample contains both solutions, the one with hardcoded criteria in Sheet2,
and the more flexible one in Sheet2 (2), for which the desired criteria can
be input within H1:H3. If it suits, I would suggest you try adapting /
implement the more flexible of the 2.

---
 
Thanks Max - you are a wonder - I now need to sit down with it and
ammend it slightly to make it fit the exact data set I have.

Presumably there would be no problem having both the 'x' criteria that
the selection are being made on and the start and end dates come from
specific cell references.

david
 
Thanks Max - you are a wonder - I now need to sit down with it and
ammend it slightly to make it fit the exact data set I have.

Presumably there would be no problem having both the 'x' criteria that
the selection are being made on and the start and end dates come from
specific cell references.

david
 
david said:
Thanks Max - you are a wonder - I now need to sit down with it and
amend it slightly to make it fit the exact data set I have.
Click to expand...

You're welcome !
Presumably there would be no problem having both the 'x' criteria that
the selection are being made on and the start and end dates come from
specific cell references.
Click to expand...

Yes, of course. I've provided a working sample, no? <g>. It's always
better to structure it to point to criteria input cells (generalize) instead
of hardcoding the criteria's specific values within the formula. That way, we
don't need to re-edit / re-fill formulas all over again should the specific
values for the criteria change.

---
 
Max

I have tried changing it but it doesn't seem to be working.

Would there be any chance of you looking at the spreadsheet if I email
it to you to see where I have gone wrong .

Thanks

David
 
david said:
I have tried changing it but it doesn't seem to be working.
Would there be any chance of you looking at the spreadsheet if I email
it to you to see where I have gone wrong .
Click to expand...

No problem*, but it'll be faster / better if you could upload** a small
sample (sanitized if necess.) & just post the link to it in response here.
Anyway pl drop me a line here to let me know if you've emailed.

*Email: demechanik <at> yahoo <dot> com
(Need to wait awhile, though, as I can only access
my yahoo account in about 9-10 hours time)

**Use either of the 3 free filehosts listed below to upload your sample and
then post the link to it in response here (the link is generated when you
upload, just copy and paste it here)

**
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click the "Browse" button,
navigate to folder > select the file > Open, then click the button centred
in the page below (labelled "Creer le lien Cjoint") and it'll generate the
link. Then copy & paste the generated link as part and parcel of your
response here.

Kindly note that no attachments should be posted *directly* to the newsgroup

---
 
david said:
I have uploaded the problem file to savefile.com
http://www.savefile.com/projects/674184
Click to expand...

Here's the implemented file:
http://cjoint.com/?hej7AWtVYv
Incident_Book_for_playY9.xls

In sheet: Printout,

In A6, A6 copied across to K6, then filled down to K100:
=IF(ROW(A1)>COUNT($O:$O),"",INDEX(Y9Incidents!A:A,MATCH(SMALL($O:$O,ROW(A1)),$O:$O,0)+8))

We need to adjust with a "+8" to the result returned by MATCH since the
source data in sheet: Y9Incidents begins in row 14 and the formula's starting
row here is row 6.

The criteria col O
In O6 filled down to say O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C$10=""),"",IF(Y9Incidents!B14="","",IF(AND(Y9Incidents!B14=Control!$C$9,Y9Incidents!D14>=Control!$C$10,Y9Incidents!D14<=Control!$C$11),ROW(),"")))
(Leave O1:O5 empty)

And for a neater look, we can suppress extraneous zeros in the sheet
via clicking: Tools > Options > View tab > Uncheck Zero values > OK

---
 
Max

Thanks once again for you patience with someone who knows enough to
know that Excel can do something but not quite enough to get it to do
it.

David
 
Max

I am being a pain again!!!

The solution you sent me has a problem.

When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.

David
 
david said:
When I click on the 'New Entry' macro on the 'Y9_Incidents' sheet it
counts the cell reference number down in the 'O' column of the
'Printout' sheet.

I tried making it an absolute rather than a relative reference but I
can't get that to work.
Click to expand...

In sheet: Printout,

Try this revised criteria formula* in O6, filled down to O100 (say):
=IF(OR(Control!$C$9="",Control!$C$10="",Control!$C$10=""),"",
IF(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),ROW(A1))="","",
IF(AND(INDEX(INDIRECT("'Y9Incidents'!B14:B1000"),ROW(A1))=Control!$C$9,
INDEX(INDIRECT("'Y9Incidents'!D14:D1000"),ROW(A1))>=Control!$C$10,
INDEX(INDIRECT("'Y9Incidents'!D14:D1000"),ROW(A1))<=Control!$C$11),ROW(),"")))

*to cater for new row insertions by the macro in Y9Incidents

(No change to the other formulas in cols A to K)

An implemented sample is available at:
http://www.savefile.com/files/5487340
incident_Book_for_playY9_1.xls

Test it out (works ok here, under light testing)

---
 
*to cater for new row insertions by the macro in Y9Incidents

Since the macro / it's impact wasn't part of your original post,
you do realize I'll be bumping up my bill to you, won't you? <bg>

---
 
Thanks Max this seems to be working OK

One final question and please ignore this if I am taking up too much of
your time.

I have tweaked the file to add summary pages for each of the 4 classes
together with the printout page which pulls its criteria from from the
'Control' sheet.

What I want to be able to do is print this 'Printout' page via a macro.
So I would select the form from the dropdown box on the control page
and then press a macro button. To do this I presume I need to be able
to detect which is the last line that there is any data in so the print
range can be set- how can I do this?


the file can be downloaded from

http://savefile.com/projects/857497

Thanks

David
 
Max

I (hopefully finally but I can't help fiddling with things) want to
create a page similar to the report for each class that could pick up
the student name from the 'Control' Page and only print the entries for
that student for the date ranges.

I anticipated a validation box on the control page that picks up the
student lists for that clas and then entering the start and end date
for this report.

I expected it would be easy to copy and print the previous report but I
get the student i want but also ohers as well.

I tried to upload thisversion to savefile.com but thre is an error and
I can't do it.

Many thanks

David
 
Back
Top