Help with returning records given a date range

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

I was wondering if anyone could give me some pointers as to where to
start with the following?

I have a spreadsheet of data in a table. One of the fields is date. I
would like the user to be able to enter a date range and have all the
records returned that fall within that range.

What function should I use for that? DGET can only return one record.
Could I use something like VLOOKUP in an array? The number of records
returned will obviously change depending on the date range entered.

Or, should I use something like a VBA loop?

I need to do this in Excel - it can't be in Access.

Any help would be much appreciated.

Many thanks - David
 
Hi!

If you can't filter:
I would like the user to be able to enter a date range and have all the
records returned that fall within that range.
Click to expand...

How many "records" are you talking about?

Be very specific. Explain it like this:

"I" have a data table in Sheet1 from A2:H500.

Column A are dates.

B2:H500 contains various data.

The user will enter a date range. A1 will hold the lower boundry and B1 will
hold the upper boundry.

"I" want to extract to Sheet2 all the data in columns B:H that fall within
the date range of cells A1 and B1 inclusive.

Biff
 
Hi Biff,

The number of records increases at around 50 per day, but I'll clear
out ones older than 3 months old so let's say there wont be more than
5000 records. I'll probably only be looking to return about 1500 at the
very most at a time.

The data table is in Sheet1 from A2:AQ500 to start with but the 500
part will obvioulsy change as time goes on.

Lets say the lower and upper bounds are in cells A1 and B1
respectively.

I'd want to extract to Sheet2 all the data in columns A:AQ that fall
within the date range of cells A1 and B1.

What's the best way?

Many thanks - David
 
Hi!

Ok, that might be too much to do with formulas. It could be done but the
calculation time would be very slow.

If you can't filter then your only remaining option is VBA code. I can't
help you with that but you should try posting this in the Programming forum.

If you don't get a solution there then post back here and let me know. I'll
put together a sample file that does this with formulas.

Biff
 
Daveo said:
.. The data table is in Sheet1 from A2:AQ500 to start with
but the 500 part will obviously change as time goes on.
Lets say the lower and upper bounds are in cells A1 and B1
respectively.
I'd want to extract to Sheet2 all the data in columns A:AQ that fall
within the date range of cells A1 and B1.
Click to expand...

Here's a non-array formulas play ..

In Sheet1
-------
Assume the dates in the source table are in col A, from A2 down

Use an empty column to the right of the table, say col AS
Put in AS2:
=IF(A2="","",IF(AND(A2>=Sheet2!$A$1,A2<=Sheet2!$B$1),ROW(),""))
Copy AS2 down till the last row of source data, i.e. to AS500
(Leave AS1 empty)

In Sheet2
----
Assume A1:B1 will house the inputs: Start date, End date
(Let's put the lower and upper bounds here in Sheet2 itself)

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$AS:$AS,ROWS($A$1:A1))),
"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$AS:$AS,
ROWS($A$1:A1)),Sheet1!$AS:$AS,0)))

Copy A2 across to AQ2, fill down to AQ500
Format col A as date

Sheet2 will return the desired results, i.e. only those lines from Sheet1
with dates in col A falling between the start* and end* dates (inclusive),
all neatly bunched at the top.
*as input in A1:B1

For a cleaner look, suppress extraneous zeros from showing in Sheet2 via:
Click Tools > Options > View tab > Uncheck "Zeros display" > OK)

Adapt to suit ..

Also, consider setting the calculation mode to manual
(via: Tools > Options > Calculation tab)
Then just press F9 to calc after the inputs are made in A1:B1
 
Daveo said:
Hi Biff,

The number of records increases at around 50 per day, but I'll clear
out ones older than 3 months old so let's say there wont be more than
5000 records. I'll probably only be looking to return about 1500 at the
very most at a time.

The data table is in Sheet1 from A2:AQ500 to start with but the 500
part will obvioulsy change as time goes on.

Lets say the lower and upper bounds are in cells A1 and B1
respectively.

I'd want to extract to Sheet2 all the data in columns A:AQ that fall
within the date range of cells A1 and B1.

What's the best way?

Many thanks - David
Click to expand...

I suppose you don't have Blanks on your Date column.
If it is not the case, please let me know.

Define StartCell, TargetCell, ColNum, LowerDate, UpperDate

-----------------------------------------------------
Sub Button36_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long

'User Definitions
'------------------------------
Set StartCell = Sheets("Sheet2").[A230]
Set TargetCell = Sheets("Sheet10").[A1]
ColNum = 4
Set LowerDate = [A228]
Set UpperDate = [A229]
'------------------------------

On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each i In Range(StartCell, StartCell.End(xlDown))
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k) = i.Offset(0, k)
Next
j = j + 1
End If
Next

ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
 
Hi Bruno,

I put this code in my sheet and changed a couple of things. My start
cell is Sheet1, Cell A3. I want the data to be copied into Sheet2,
starting at Cell A5. My dates are in Sheet1, Cells A1 and B1. I have 44
columns that I need to copy. When I run the code, nothing happens at
all:


Private Sub CommandButton1_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long


'User Definitions
'------------------------------
Set StartCell = Sheets("Sheet1").[A3]
Set TargetCell = Sheets("Sheet2").[A5]
ColNum = 44
Set LowerDate = Sheets("Sheet1").[A1]
Set UpperDate = Sheets("Sheet1").[B1]
'------------------------------


On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


For Each i In Range(StartCell, StartCell.End(xlDown))
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k) = i.Offset(0, k)
Next
j = j + 1
End If
Next


ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Thanks - David
 
Hi Bruno,

I put this code in my sheet and changed a couple of things. My start
cell is Sheet1, Cell A3. I want the data to be copied into Sheet2,
starting at Cell A5. My dates are in Sheet1, Cells A1 and B1. I have 44
columns that I need to copy. When I run the code, nothing happens at
all:
Click to expand...
[...]

Ok Daveo.
First of all, are you sure your range starting with Sheet1!A3,
cell Sheet1!A1, cell Sheet1!B1 have all the same data type?
Things may be different from what they appear to be.
Then try REMing out the following 3 lines:

On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

and tell me please what line of code Debugger points
to for error.

Bruno
 
Hi Bruno,

The range has all the same data type (dd/mm/yy).

I remmed out the error handling and the debugger pointed to the
following line:

For Each i In Range(StartCell, StartCell.End(xlDown))

With the error message:

Run-time error '1004':

Method 'range' of object '_Worksheet' failed


Many thanks - David
 
Daveo said:
Hi Bruno,

The range has all the same data type (dd/mm/yy).

I remmed out the error handling and the debugger pointed to the
following line:

For Each i In Range(StartCell, StartCell.End(xlDown))

With the error message:

Run-time error '1004':

Method 'range' of object '_Worksheet' failed


Many thanks - David
Click to expand...

I've reproduced your very same environment and I confirm
the procedure is working properly.
May be your Excel version is unable to read that For Each ...
line of code.
I've made some slight modification:
1 - add to Dim Section: Dim OperatingRange as Range
2 - replace the For Each line with the following ones:
Set OperatingRange = Range(StartCell, StartCell.End(xlDown))
For Each i In OperatingRange
And please tell me what Excel version you're using;
here using Excel 2003.

Bruno
 
Hi Bruno,

Thanks for the reply. However I get the same error msg as before at the
following line:

Set operatingRange = Range(StartCell, StartCell.End(xlDown))

I'm using Excel 2002 SP2.

Thanks - David
 
Hi Bruno.

The following works:

Private Sub CommandButton1_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long
Dim operatingRange As Range



'User Definitions
'------------------------------

Set StartCell = Sheets("Data").Range("A3")
Set LowerDate = Sheets("Data").Range("A1")
Set UpperDate = Sheets("Data").Range("B1")
Set TargetCell = Sheets("Sheet2").Range("A5")
ColNum = 45

'------------------------------


On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Set operatingRange = Sheets("Data").Range(StartCell,
StartCell.End(xlDown))
For Each i In operatingRange
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k).Value = i.Offset(0, k).Value
Next
j = j + 1
End If
Next


ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Thanks for all your help - David
 
Daveo said:
Hi Bruno.

The following works: [...]
Set StartCell = Sheets("Data").Range("A3")
Set LowerDate = Sheets("Data").Range("A1")
Set UpperDate = Sheets("Data").Range("B1")
Set TargetCell = Sheets("Sheet2").Range("A5")
ColNum = 45
Click to expand...
[...]

Well, Excel 2002 SP2 doesn't recognise [A3] as
a shortcut for Range("A3").
Useful to know!

Ciao
Bruno
 
Back
Top