Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- BrianDP1977 ------------------------------------------------------------------------ BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110 View this thread: http://www.excelforum.com/showthread...hreadid=492041 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge ranges based on condition
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge ranges based on condition
That should do the trick. Thank you -- BrianDP197 ----------------------------------------------------------------------- BrianDP1977's Profile: http://www.excelforum.com/member.php...fo&userid=2911 View this thread: http://www.excelforum.com/showthread.php?threadid=49204 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge ranges based on condition
You're welcome.
BrianDP1977 wrote: That should do the trick. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge two columns of data with a condition | Excel Worksheet Functions | |||
setting more than one condition and multiple ranges | Excel Discussion (Misc queries) | |||
SUMIF with two condition from two ranges | Excel Worksheet Functions | |||
Sum If with two ranges and condition | Excel Worksheet Functions | |||
Counting Across Multiple Ranges, Based on Condition | Excel Worksheet Functions |