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

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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!D 2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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 wrote:
Put in G2:
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2 =--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))

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!D 2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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 wrote:
Put in G2:
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2 =--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))

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!D 2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"david" wrote:
I am a bit confused as to what the ($G:$G) section of the formula is ..

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.

=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?

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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 wrote:
Put in G2:
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2 =--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))

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!D 2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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 wrote:
Put in G2:
=IF(Sheet1!C2="","",IF(AND(Sheet1!C2="x",Sheet1!D2 =--"1-May-2006",Sheet1!D2<=--"31-May-2006"),ROW(),""))

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!D 2<=$H$3),ROW(),"")))

Then just change/input the criteria values within H1:H3
to obtain the required results in cols A to G
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---