Merge ranges based on condition

On one sheet (“Enter Info”) I have three sections of named ranges of names (A_Names, B_Names, and C_Names) and each name within each range has a date associated with it. These dates are also clumped in respective named ranges (A_Dates, B_Dates, and C_Dates) as so:

(A_Names) (A_Dates)
Brad ......... 12 NOV 05
Tom ......... 15 DEC 05
Dave ........ 28 JAN 06

(B_Names) (B_Dates)
Chad ........ 3 MAR 06
Tim .......... 7 DEC 05
Steve ........ 13 AUG 06

(C_Names) (C_Dates)
Jill ............ 8 FEB 06
Beth ......... 22 NOV 05
Greg ......... 2 DEC 05

What I would like to have happen is that, upon comparison with today’s date (an actual updating today’s date), if a person’s associated date within any of the three original named sections is after today’s date, I want it to be stored in an array and displayed on another sheet (“Output Data”) within a named range of cells (“Output_Names”). As time progresses, the name should be removed since it no longer fills the after today’s date requirement.

I’m not sure about the whole array thing (do I need one for something like this) or can I simply have code run that will input the names matching the condition onto a sheet? An example output for today's date (8 DEC 05) would be:

(Output_Names)
Tom
Dave
Chad
Steve
Jill

It may be just as easy to do this without the use of an array. I have
assumed that the date associated with each name is in the column
immediately right of the name.

Sub gtData()
Dim Nms As Range
Dim cell As Range
Dim r As Long

r = 2
On Error Resume Next
Sheets("Output Data").Range("Output_Names").ClearContents
On Error GoTo 0

With Sheets("Enter Info")
Set Nms = Union(.Range("A_Names"), .Range("B_Names") _
, .Range("C_Names"))
End With
For Each cell In Nms
If cell.Offset(0, 1).Value > Date Then
Sheets("Output Data").Cells(r, 1).Value = cell.Value
r = r + 1
End If
Next cell

Sheets("Output Data").Range("A2:A" & r - 1).Name = "Output_Names"
End Sub

Hope this helps
Rowan

BrianDP1977 wrote:
> On one sheet (“Enter Info”) I have three sections of named ranges of
> names (A_Names, B_Names, and C_Names) and each name within each range
> has a date associated with it. These dates are also clumped in
> respective named ranges (A_Dates, B_Dates, and C_Dates) as so:
>
> (A_Names) (A_Dates)
> Brad ......... 12 NOV 05
> Tom ......... 15 DEC 05
> Dave ........ 28 JAN 06
>
> (B_Names) (B_Dates)
> Chad ........ 3 MAR 06
> Tim .......... 7 DEC 05
> Steve ........ 13 AUG 06
>
> (C_Names) (C_Dates)
> Jill ............ 8 FEB 06
> Beth ......... 22 NOV 05
> Greg ......... 2 DEC 05
>
> What I would like to have happen is that, upon comparison with today’s
> date (an actual updating today’s date), if a person’s associated date
> within any of the three original named sections is after today’s date,
> I want it to be stored in an array and displayed on another sheet
> (“Output Data”) within a named range of cells (“Output_Names”). As time
> progresses, the name should be removed since it no longer fills the
> after today’s date requirement.
>
> I’m not sure about the whole array thing (do I need one for something
> like this) or can I simply have code run that will input the names
> matching the condition onto a sheet? An example output for today's date
> (8 DEC 05) would be:
>
> (Output_Names)
> Tom
> Dave
> Chad
> Steve
> Jill
>
>

That should do the trick. Thank you.

You're welcome.

BrianDP1977 wrote:
> That should do the trick. Thank you.
>
>