• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate() - modified

Suril

Member
The concatenate function or the & operator is pretty straightforward.. However, one of the things which comes out as a limitation to me was this: all the cells have to be referred by sepecting with , and not a range of cells.
eg: cell A1 to cell Z1 have to be concatenated.. I have to refer cell a1, b1, c1....... Instead, I wrote a very simple code to simply refer: A1:Z1

Code:
Function concat(rng As range) As String
For Each c In rng
concat = concat & c.Value
Next
End Function
 

Suril

Member
I am sure there would be many modifications required to this.. Please make me happy with your criticisms.
 

Suril

Member
Hi @Aurelie,

Thanks! didnt know that chandoo had written a post on this.. that helps a lot.. havent intvestigated it but i just thot wat was the need to have such an elaborate code when this simple code does it all... I guess i may b missing something
 

Aurelie

Member
To be honest I don't need this kind of formula so I did not check the code in details, but I would start there because I'm far from being an expert in VBA (not yet :(). However, it would be interesting to compare with your code and check if it would work the same way, and if not, why.

Cheers,
 

Hui

Excel Ninja
Staff member
Suril

The link posted above contains many examples in the Comments section of Concatif with many, many options
 
Sorry for resurrecting an old thread but I thought I would share my own version of conditional concatenation.

Like many others I agree that Excel doesn't include enough functions in its' library for string manipulation. Generally I'm not a big fan of UDF's but I think this is where UDF's become important.

I had a requirement to perform conditional concatenation and when writing the UDF I thought I'd introduce two things into my code:
  1. I want to perform any type of comparison (not just "equals"). Much like the way we utilise the operators with SUMIF(S)/COUNTIF(S)...
  2. I want to avoid looping through ranges and/or arrays so as not to write a slow-calculating function; and rather evaluate an array formula that generates the desired array for concatenation (and use the Join function to actually string the values together).
I ended up with this:
Code:
Public Function ConcatenateIf(ByVal rngCriteriaRange As Excel.Range, _
                              ByVal varCriteria As Variant, _
                              ByVal rngValues As Excel.Range, _
                              Optional ByVal strDelimiter As String = " ") As Variant
    Dim lngRows As Long, lngCols As Long
    Dim blnErr As Boolean, lngErr As XlCVError
    Dim strCritAddress As String
    Dim strValAddress As String
    Dim varOperators As Variant: varOperators = VBA.Array("=", "<>", ">", "<", ">=", "<=")
    Dim strOperator As String
    Dim varResults As Variant

    With rngCriteriaRange
        lngRows = .Rows.Count
        lngCols = .Columns.Count
    End With

    '#REF! if 2D criteria range is passed
   blnErr = CBool(lngRows > 1 And lngCols > 1)
    If blnErr Then
        lngErr = xlErrRef
        GoTo err_exit
    End If

    '#VALUE! if values range dimension is not the same size and orientation as criteria range
   With rngValues
        blnErr = CBool(lngRows <> .Rows.Count)
        blnErr = CBool(blnErr Or lngCols <> .Columns.Count)
        If blnErr Then
            lngErr = xlErrValue
            GoTo err_exit
        End If
    End With

    '#N/A if the criteria is an array (or more than one cell)
   blnErr = IsArray(varCriteria)
    If blnErr Then
        lngErr = xlErrNA
        GoTo err_exit
    End If

    'Split the operator from the criteria, if an operator has been included
   strOperator = Left$(varCriteria, 2)
    If IsNumeric(Application.Match(strOperator, varOperators, 0)) Then
        varCriteria = Mid$(varCriteria, 3)
    Else
        strOperator = Left$(varCriteria, 1)
        If IsNumeric(Application.Match(strOperator, varOperators, 0)) Then
            varCriteria = Mid$(varCriteria, 2)
        Else
            strOperator = "="
        End If
    End If

    'Make sure the criteria type is correct, and concatenate the operator with the criteria
   If IsDate(varCriteria) Then
        varCriteria = strOperator & CDbl(varCriteria)
    Else
        If IsNumeric(varCriteria) Then
            varCriteria = strOperator & varCriteria
        Else
            varCriteria = strOperator & Chr$(34) & varCriteria & Chr$(34)
        End If
    End If

    'Get the addresses of the criteria and values ranges
   strCritAddress = rngCriteriaRange.Address(external:=True)
    strValAddress = rngValues.Address(external:=True)

    'Construct an array of the results
   If lngRows > 1 Then
        varResults = Evaluate("transpose(if(" & strCritAddress & varCriteria & "," & strValAddress & "))")
    Else
        varResults = Evaluate("if(" & strCritAddress & varCriteria & "," & strValAddress & ")")
    End If

    'Remove non-matching items from the array, and concatenate the remaining items
   varResults = Filter(varResults, False, False)
    ConcatenateIf = Join$(varResults, strDelimiter)

    Exit Function

err_exit:
    ConcatenateIf = CVErr(lngErr)
End Function
Syntax: =ConcatenateIf(criteria_range, criteria, values_range, delimiter)

This function has since served me well which is why I thought this might be a good place to share it. I include this link to my now very-out-of-date blog where the syntax is explained and where samples (and a sample file) can be studied.
 

bobhc

Excel Ninja
Good day Jon

A nice link to your site with a brilliant and clear explanation, will be looking at other parts when time permits.
 
Top