Concatenate with date range

[Pete@cadth]

Hi,

I have 3 columns and multiple rows.
Column A contains the product name
Column B contains the start date
Column C contains the completion date

I would like to concatenate all the product names within a specified date
range into 1 cell. I already have my date range identified in another tab
(i.e. FY!E3:E4)
Any ideas how I can do this?

Thanks.

 

[Joel]

You can use a UDF function like below


call with
=CatNames(A1:A100, FY!E3:E4)



Function CatNames(Target As Range, Dates As Range)
StartDate = Dates(1)
EndDate = Dates(2)

CatNames = ""
For RowOffset = 1 To Target.Rows.Count
If (Target(RowOffset, 2) >= StartDate And _
Target(RowOffset, 2) <= EndDate) Or _
(Target(RowOffset, 3) >= StartDate And _
Target(RowOffset, 3) >= EndDate) Then

CatNames = CatNames & "," & Target(RowOffset, 1)
End If
Next RowOffset

End Function

 

[Sheeloo]

Best way is to write a Macro which will loop through the range and concatenate.

Concatenate has a limit of 255 cells. One way is to write a formula like the
following, copy it into the number of entries in your range, and concatenate
the formula into the result cell -
=IF(AND(IF(B1>FY!$E$3,1,0),IF(C1<FY!$E$4,1,0)),A1,"")

 

[Joel]

When you have a range of dates usally you want to have if any of the dates in
the range are within a start date and an end date. Your case only considers
if the range of dates are completely withiin the start and end dates.

 

[Sheeloo]

If you want any date then you may replace AND with OR.

 

[Joel]

OR will not work!!!

if your range is between 8/1/08 and 8/31/08 9/1 will pass the OR test.

 

[Pete]

Thanks Joel. I'm a beginner with VBA. Would it be possible to add comments
to your function so that I can better understand the logic?

I assume that I need to replace Dates(1) and Dates(2) with actual dates?

 

[Joel]

Function CatNames(Target As Range, Dates As Range)
'Start Date is 1st parameter in Dates
StartDate = Dates(1)
'End Date is 2nd parameter in Dates
EndDate = Dates(2)

CatNames = ""
'Check each Row in Target range for dates
' between start and end dates
For RowOffset = 1 To Target.Rows.Count
'check if start date in Row is greater then
'selected start date and less than selected end date
'or
'end date in row is greater than start date
'and less the selected end date
If (Target(RowOffset, 2) >= StartDate And _
Target(RowOffset, 2) <= EndDate) Or _
(Target(RowOffset, 3) >= StartDate And _
Target(RowOffset, 3) >= EndDate) Then

'if the dates are in range then Catenate the
'data in column A
CatNames = CatNames & "," & Target(RowOffset, 1)
End If
Next RowOffset

End Function