Concatenate with criteria

[KIM Chanthan]

Dear Sir,

Another question of mine is to concatenate with criteria.
So do you have any rule to fix that.
Please find attached file to be clear about my question.

Thank,
Chanthan

 

[Hui]

Kim

Have a read through: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

There is many different examples of Concatif() functions available there and in the comments

I modified a simple version to:

Function ConcatIf(Src As Range, ChkRng As Range, myVal As String, Optional Exc As Boolean = False, 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 Exc Then
  If c <> myVal Then
  If WorksheetFunction.IsNumber(Src(i)) Then
  retVal = retVal + Trim(Str(Src(i))) + Sep
  Else
  retVal = retVal + Src(i) + Sep
  End If
  End If
  Else
  If c <> myVal Then
  If WorksheetFunction.IsNumber(Src(i)) Then
  retVal = retVal + Trim(Str(Src(i))) + Sep
  Else
  retVal = retVal + Src(i) + Sep
  End If
  End If
  End If
  i = i + 1
Next
ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
End Function

To use:
=ConcatIf(A1:C1,A2:C2,"",TRUE)

Then True tells excel to Exclude the ""

see attached file: