Copying rows from 2 sheets to a new worksheet based on date criter

G

Guest

Hi All,

I am trying to build a maintenance cntract tracking system. This workbook
contains all my infrastructure assets, maintenance start/end dates, contact
details, contract numbers, costs and other miscellaneous data. One of the
results I need to produce is a list of all maintenance contracts that will
expire by 30th of the next calendar month. Eg. If we are in August, I want to
return anything that will expire before 30th September in the current year.

I have the asset name in column A of sheet1 and sheet2. I need to extract
only the rows based on the date in column U in sheet2 and merge colums A and
U from sheet 1 with columns O, S, U and V from sheet2 and place the results
in Sheet3.

Sheet3 has headings and other content, so the placement needs to start at
row 10. As the result list length could be variable, and my total length of
the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
the end of the extract in sheet3 are blanked out.

Is this possible in Excel 2003 natively? or will it require scripting? If
so, could you please suggest appropriate code.

Many thanks,
Phillip Morgan
 
G

Guest

One approach using non-array formulas which should deliver it for you ..

Illustrated in this "plug-n-play" sample construct:
http://www.savefile.com/files/994757
Maintenance Contract Tracking Sys.xls

Assume source data as posted in row2 down
in sheets: X (your sheet1) and Y (your sheet2)
[I like to use v.short sheetnames, makes formulas v.shorter]

In a sheet: Z (this is your sheet3),

In A10:
=IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))

In B10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))

In C10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))

In D10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))

In E10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))

In F10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))

In G10:
=IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))

Select A10:G10, copy down to G300. Cols B to G will auto-return the required
results from sheets X and Y, with all lines neatly bunched at the top.
 
G

Guest

Hi Max,

Thanks for your response. Unfortunately it did not yeild the expected results.

I got no asset names in sheet 3, just blank cells and numbers.
Dates were retured as 01/01/1900 for some assets. Column B returned 0 for
each row. All $ values were 0. The last column, which should have been a text
field (contract number), just returned #N/A. The rows were not bunched.

Also, I should have mentioned, ALL sheets have the headings on row 9. Data
always starts at row 10 in every sheet (as such I changed U2 in formula in
A10 to U10).

Phill


Max said:
One approach using non-array formulas which should deliver it for you ..

Illustrated in this "plug-n-play" sample construct:
http://www.savefile.com/files/994757
Maintenance Contract Tracking Sys.xls

Assume source data as posted in row2 down
in sheets: X (your sheet1) and Y (your sheet2)
[I like to use v.short sheetnames, makes formulas v.shorter]

In a sheet: Z (this is your sheet3),

In A10:
=IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))

In B10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))

In C10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))

In D10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))

In E10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))

In F10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))

In G10:
=IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))

Select A10:G10, copy down to G300. Cols B to G will auto-return the required
results from sheets X and Y, with all lines neatly bunched at the top.

---
Phill_Morgan said:
Hi All,

I am trying to build a maintenance cntract tracking system. This workbook
contains all my infrastructure assets, maintenance start/end dates, contact
details, contract numbers, costs and other miscellaneous data. One of the
results I need to produce is a list of all maintenance contracts that will
expire by 30th of the next calendar month. Eg. If we are in August, I want to
return anything that will expire before 30th September in the current year.

I have the asset name in column A of sheet1 and sheet2. I need to extract
only the rows based on the date in column U in sheet2 and merge colums A and
U from sheet 1 with columns O, S, U and V from sheet2 and place the results
in Sheet3.

Sheet3 has headings and other content, so the placement needs to start at
row 10. As the result list length could be variable, and my total length of
the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
the end of the extract in sheet3 are blanked out.

Is this possible in Excel 2003 natively? or will it require scripting? If
so, could you please suggest appropriate code.

Many thanks,
Phillip Morgan
Click to expand...
Click to expand...
 
G

Guest

jeez, how do I edit my own posts...

I adjusted the +1 in the rows to start at row 10 and the dates and dollars
show up fine, the only things not working is showing the asset name, and the
contract number is not finding a match, and so returning #N/A.

Also, does it matter than column U in sheet 2 is a calculated value? The
maintenance start date + number of years of contract generates the end date
of the contract.

phill
Phill_Morgan said:
Hi Max,

Thanks for your response. Unfortunately it did not yeild the expected results.

I got no asset names in sheet 3, just blank cells and numbers.
Dates were retured as 01/01/1900 for some assets. Column B returned 0 for
each row. All $ values were 0. The last column, which should have been a text
field (contract number), just returned #N/A. The rows were not bunched.

Also, I should have mentioned, ALL sheets have the headings on row 9. Data
always starts at row 10 in every sheet (as such I changed U2 in formula in
A10 to U10).

Phill


Max said:
One approach using non-array formulas which should deliver it for you ..

Illustrated in this "plug-n-play" sample construct:
http://www.savefile.com/files/994757
Maintenance Contract Tracking Sys.xls

Assume source data as posted in row2 down
in sheets: X (your sheet1) and Y (your sheet2)
[I like to use v.short sheetnames, makes formulas v.shorter]

In a sheet: Z (this is your sheet3),

In A10:
=IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))

In B10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))

In C10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))

In D10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))

In E10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))

In F10:
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))

In G10:
=IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))

Select A10:G10, copy down to G300. Cols B to G will auto-return the required
results from sheets X and Y, with all lines neatly bunched at the top.

---
Phill_Morgan said:
Hi All,

I am trying to build a maintenance cntract tracking system. This workbook
contains all my infrastructure assets, maintenance start/end dates, contact
details, contract numbers, costs and other miscellaneous data. One of the
results I need to produce is a list of all maintenance contracts that will
expire by 30th of the next calendar month. Eg. If we are in August, I want to
return anything that will expire before 30th September in the current year.

I have the asset name in column A of sheet1 and sheet2. I need to extract
only the rows based on the date in column U in sheet2 and merge colums A and
U from sheet 1 with columns O, S, U and V from sheet2 and place the results
in Sheet3.

Sheet3 has headings and other content, so the placement needs to start at
row 10. As the result list length could be variable, and my total length of
the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
the end of the extract in sheet3 are blanked out.

Is this possible in Excel 2003 natively? or will it require scripting? If
so, could you please suggest appropriate code.

Many thanks,
Phillip Morgan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Phill_Morgan said:
jeez, how do I edit my own posts...

I adjusted the +1 in the rows to start at row 10 and the dates and dollars
show up fine, the only things not working is showing the asset name, and the
contract number is not finding a match, and so returning #N/A.
Click to expand...

Here's the revised working construct to suit your actuals:
http://cjoint.com/?iygs4bCaH0
Maintenance Contract Tracking Sys_v1.xls

The adjustment to the formulas in B10:F10 is just change the "+1" to "+9"
Also, does it matter than column U in sheet 2 is a calculated value? The
maintenance start date + number of years of contract generates the end date
of the contract.
Click to expand...

Provided the dates calculated are real dates (like the dummy real dates in
my sample), it shouldn't matter. If you experience problems, post your
formula calculating the dates.

---
 
G

Guest

Sorry for the delay in responding. Weekend :)

It doesn't work. This is the results I get.


0 1/01/2001 13/08/200711/08/2008 55641.36 #N/A
0 1/12/2006 1/12/2008 30/11/2010 144000.00 #N/A
0 0/01/1900 0/01/1900 0.00 #N/A

Column A is blank. B is not showing the Asset name, and Contract # is #N/A.

The date being calcualted for End date in sheet 2 (column U), is

=IF(S10<>"",S10+((T10-1)*365)+364,"")

T10 is ther term of the contract in years. For leap years, this ends up
being a day out, but I can live with that. This date is then formatted as
DD/MM/YYYY (AU format)

Phill
 
M

Max

It doesn't work.

well, think it works, as evidenced in the latest sample earlier, which I had
thought was customised to fit 99.9%.

Presume this is in U10:
=IF(S10<>"",S10+((T10-1)*365)+364,"")
Click to expand...
where S10 contains the start-date, and T10 contains a number (no. of years
of contract)

To derive the end-dates automatically, suggest you replace the above with
this expression using DATE in U10:
=IF(S10<>"",DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1))
Copy U10 down. That should return the exact end-dates in col U.

As for your overall adaptation difficulties, perhaps you could use either of
the 2 filehosts below to upload a sample of your actuals (Desensitize it
first). Then copy n paste the generated link to your sample file in response
here:

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


---
 
G

Guest

Max,

thanks so much for your help. I found the problem.
I had column A hidden in sheet one. The asset name was actually in column B,
so the reference to A:A and $A$A needed to be changed to B:B and $B$B
respectively.

this fixed the problem.

thanks so much for your assistance. Greatly appreciated.

Phill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Matching Columns in work sheets and copying both rows to new 1
Need a macro to combine the data from sheets 3
Need a Macro to combine 1-5 sheets in one sheet 1
Subtotalling Filtered Data Across Sheets 3
How to insert new worksheet based on # of rows in a data sourch 2
how to select and count from a range of data 2
Selective rows from one sheet to another 8
How to filter rows data based on background color of individual cells 1

Top