Concatenate with date range
 

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.

Concatenate with date range
 

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



"Pete@cadth" wrote:

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.

Concatenate with date range
 

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(B1FY!$E$3,1,0),IF(C1<FY!$E$4,1,0)),A1, "")


"Pete@cadth" wrote:

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.

Concatenate with date range
 

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" wrote:

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(B1FY!$E$3,1,0),IF(C1<FY!$E$4,1,0)),A1, "")


"Pete@cadth" wrote:

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.

Concatenate with date range
 

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

"Joel" wrote:

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" wrote:

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(B1FY!$E$3,1,0),IF(C1<FY!$E$4,1,0)),A1, "")


"Pete@cadth" wrote:

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.

Concatenate with date range
 

OR will not work!!!

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

"Sheeloo" wrote:

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

"Joel" wrote:

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" wrote:

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(B1FY!$E$3,1,0),IF(C1<FY!$E$4,1,0)),A1, "")


"Pete@cadth" wrote:

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.

Concatenate with date range
 

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" wrote:

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



"Pete@cadth" wrote:

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.

Concatenate with date range
 

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


"Pete" wrote:

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" wrote:

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



"Pete@cadth" wrote:

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.