Copying rows with specific criteria

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I have a worksheet that has a list of all my open orders. Column G has the
due date of these orders. What I want to do is copy all the orders that are
past due as of today's date and paste them into another worksheet. This will
change daily since orders will ship and no longer be past due. I want to be
able to run this daily and have it overwrite the rows I copied the previous
day. How would I go about setting this up? I'm not very familiar with VBA.
 
One simple non-array formulas play which might appeal to you ..

Assume source data in Sheet1's cols A to G, data from row2 down
Col G is the key col, with real dates

In Sheet1,
Put in H2: =IF(G2="","",IF(G2<TODAY(),ROW(),""))
Copy H2 down to cover the max expected extent of data in col G, eg down to
F2000? Leave H1 blank. This is the criteria col.

Then in another sheet,
Put in A2
=IF(ROWS($1:1)>COUNT(Sheet1!$H:$H),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$H:$H,ROWS($1:1))))
Copy A2 across to G2, fill down to cover the same extent as done in Sheet1's
col F. Cols A to G will auto-return the required results* from Sheet1, with
all lines neatly bunched at the top. *all orders that are past due as of
today's date
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
 
Back
Top