Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate cells by a given range | Excel Worksheet Functions | |||
function to concatenate range | Excel Discussion (Misc queries) | |||
concatenate a range function | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Concatenate a range | Excel Worksheet Functions |