Conditional Concatenate formula Excel 2003

[Ria]

Hi,

I am struggling with little formula in EXCEL 2003. Trying to concatenate range of cells based on if condition but no success so far.
I have range of cells need to concatenate: N1:N20 and range O1:O20 has either Y or empty or N. I want to look in range O1:O20, if cell value is: Y (more than one cell have Y value) then concatenate relative values from range N1:N20. I am trying to avoid vba code cause I am not good in that. Here I have one formula but it looks first cell in range and does not look in next cell: an array formula
{=IF($O$1:$O$20="Y",CONCATENATE($N$1:$N$20&"/"),"")}

Any help would be appreciated.

Thanks

Ria

 

[Hui]

I assume you want to concatenate the cells in N1:N20 where the cell in O1:O20 is "Y"

If that is the case you cannot use Concatenate in that way

I'd suggest reading:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Also read the comments as there are several extensions to the main Concatif() UDF discussed

 

[Ria]

Thanks HUI:

I looked at code in the link but it does not tell which range and where to start/end looking. I thought may be a solution without vba.

Regards

Ria

 

[Hui]

Ria

Excel doesn't have great inbuilt string handling functions.

The lack of Concatenate to be even able to join a range like =Concatenate(A1:A10) is to me a big oversite by the original coders, especially when so many other functions all work with ranges

For your example, you need to use the Concatif() function I wrote in the comments in the above link

Copy the following into a Code module in VBA

Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
If c = myVal Then
retVal = retVal + Src(i) + Sep

End If
i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) – Len(Sep))
End Function

Then in use in the workbook enter
=Concatif(N1:N20, O1:O20, "Y")

If you want to separate each value with a delimiter
=Concatif(N1:N20, O1:O20, "Y", ", ")

 

[Ria]

Than

Ria

Excel doesn't have great inbuilt string handling functions.

The lack of Concatenate to be even able to join a range like =Concatenate(A1:A10) is to me a big oversite by the original coders, especially when so many other functions all work with ranges

For your example, you need to use the Concatif() function I wrote in the comments in the above link

Copy the following into a Code module in VBA

Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
If c = myVal Then
retVal = retVal + Src(i) + Sep

End If
i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) – Len(Sep))
End Function

Then in use in the workbook enter
=Concatif(N1:N20, O1:O20, "Y")

If you want to separate each value with a delimiter
=Concatif(N1:N20, O1:O20, "Y", ", ")

Thanks Hui for clarifying.
I put code in vba module (in same workbook, where want to use concatif function). Then put =Concatif(N1:N20, O1:O20, "Y") formula in one cell in same work book. In vba module, it gives SYNTEX ERROR following line of code:
ConcatIf = Left(retVal, Len(retVal) – Len(Sep))

and in excel cell shows: #VALUE

Looks like I am still missing something or doing wrong. Please let me know where I am wrong.

Thanks,

Ria

 

[Hui]

Can you post the file here please?

 

[Ria]

Can you post the file here please?

Hi HUI:

Attached is file. Actual file is too big and I have to delete/mess a lot. So I created a dummy file. I hope it will help.

Thanks,

Ria

 

[Hui]

I think one of the characters, the -, in the last line was corrupted

Try this code in a Code Module :

Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
  If c = myVal Then
  retVal = retVal + Src(i) + Sep

  End If
  i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
End Function

as it works now:


or see attached file:

 

[Ria]

I think one of the characters, the -, in the last line was corrupted

Try this code in a Code Module :

Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
  If c = myVal Then
  retVal = retVal + Src(i) + Sep

  End If
  i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
End Function

as it works now:
View attachment 15546

or see attached file:

Thanks a lot HUI.

It is great and working as required.

Ria