Can this be done

[HansV]

Welcome to Eileen's Lounge!

Please note that the function is named ConcatenateIfs, not ConcatentateIfs.

Also: make sure that you haven't named the module that contains the function ConcatenateIfs - having a function and a module with the same name confuses Excel.

[diedrich08]

Hi Hans,

Thanks for the quick response. I have gone in and renamed the module to "Concatenate_ifs" and I am using ConcatenateIfs. In doing so I am still getting the "#NAME" error. I have included a screen shot below of the my workbook and VBA code. Can you have a look at let me know if anything stands out to you?

Concatifs1.png

Concatenateifs2.png

Again, your help is greatly appreciated.

[HansV]

Thanks - that helps!

You have stored the ConcatenateIfs function in a module in your personal macro workbook Personal.xlsb. That is great - it means that the function will be available in all your workbooks. BUT... since the function is not stored in the workbook in which you use it, you must use it like this:

=PERSONAL.XLSB!ConcatenateIFs(A1:A6,B1:B6,2,C1:C6,"b")

[diedrich08]

Amazing!
Thanks for the help!

[HansV]

You're welcome!

[diedrich08]

Hi Hans,
Thanks again for all your help on this function. One last question for you (hopefully!): I would like my final separator in the concatenateifs function to be "and". Right now the separator is set as "," and I was hoping I could retain this but have the separator between the second last and last items separated by "and". For example: with the current function, if A, B and C would have a result of "A, B, C". I would like it to be "A, B and C". Is this possible?
Thanks!

[HansV]

That requires the function to be modified substantially. Here is a new version:

Code: Select all

Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, August 2016
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim FinalSep As String
    Dim TempSep As String
    Dim strResult As String
    Dim lngCount As Long
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n < 2 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    Select Case n Mod 3
        Case 0
            ' Separator specified
            Separator = Criteria(n)
            FinalSep = Separator
        Case 1
            ' Two separators specified
            Separator = Criteria(n - 1)
            FinalSep = Criteria(n)
        Case 2
            ' Default separators
            Separator = ","
            FinalSep = ","
    End Select
    TempSep = Chr(182)
    ' Loop through the cells of the concatenate range
    For i = 1 To ConcatenateRange.Count
        ' Start by assuming that we have a match
        f = True
        ' Loop through the conditions
        For c = 0 To n - 2 Step 3
            ' Does cell in criteria range match the condition?
            Select Case Criteria(c + 1)
                Case "<="
                    If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<"
                    If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">="
                    If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">"
                    If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<>"
                    If Criteria(c).Cells(i).Value Like Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case Else
                    If Not Criteria(c).Cells(i).Value Like Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
            End Select
        Next c
        ' Were all criteria satisfied?
        If f Then
            ' If so, add separator and value to result
            strResult = strResult & TempSep & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        ' Remove first separator
        strResult = Mid(strResult, Len(TempSep) + 1)
        lngCount = (Len(strResult) - Len(Replace(strResult, TempSep, ""))) / Len(TempSep)
        ' Replace TempSep with Separator
        strResult = Replace(strResult, TempSep, Separator, 1, lngCount - 1)
        ' Replace last separator with FinalSep
        strResult = Replace(strResult, TempSep, FinalSep)
    End If
    ConcatenateIfs = strResult
    Exit Function
ErrHandler:
    ConcatenateIfs = CVErr(xlErrValue)
End Function

The syntax is now

=ConcatenateIfs(RangeToConcatenate, CriteriaRange1, Operator1, Condition1 [, CriteriaRange2, Operator2, Condition2, ...] [, Separator] [, FinalSeparator])

In addition to the string to be used as separator, you can specify a string to be used as final separator. For example:

=ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")

or if the function is in your Personal Macro Workbook:

=PERSONAL.XLSB!ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")

This concatenates the cells of A1:A6 where the corresponding cell in B1:B6 equals 2 and the corresponding cell in C1:C6 equals b. The separator is ", ", i.e. a comma followed by a space. The final separator is " and ".

[diedrich08]

Works great,

Thanks again VBA master!

[pandrews]

This very useful for my needs too - thanks.

One more thing, can it be edited further to ignore blank cells as the latest version appears to treat all empty cells as a criteria and therefore concatenated all the values together.

Many thanks.

Edit: In the cells I already have some text i.e. 'Mary, Lamb, Sheep' and this VB will see this as a string rather than the words invdividually and would do the following:

'Mary, Lamb, Sheep'
'Lamb, Mary, Sheep'

would result in 'Lamb, Mary, Sheep, Mary, Lamb, Sheep'

Is it possible for the VB to treat all words in a string as seperate words?

[HansV]

Welcome to Eileen's Lounge!

Here is a new version that ignores empty values and processes individual words. It assumes that the words in the cells are delimited by the same separator as the one used to concatenate the values.

Code: Select all

Function ConcatenateIfsList(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, August 2014
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim strResult As String
    Dim arrWords() As String
    Dim j As Long
    Dim col As Collection
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n < 2 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    If n Mod 3 = 0 Then
        ' Separator specified explicitly
        Separator = Criteria(n)
    Else
        ' Use default separator
        Separator = ","
    End If
    ' Initialize collection of unique items
    Set col = New Collection
    ' Loop through the cells of the concatenate range
    For i = 1 To ConcatenateRange.Count
        ' Start by assuming that we have a match
        f = True
        ' Loop through the conditions
        For c = 0 To n - 1 Step 3
            ' Does cell in criteria range match the condition?
            Select Case Criteria(c + 1)
                Case "<="
                    If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<"
                    If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">="
                    If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">"
                    If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<>"
                    If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case Else
                    If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
            End Select
        Next c
        ' Were all criteria satisfied?
        If f Then
            ' Split the cell value
            arrWords = Split(ConcatenateRange.Cells(i).Value, Separator)
            For j = 0 To UBound(arrWords)
                ' Add word to collection, if it has not been added yet
                On Error Resume Next
                col.Add Item:=arrWords(j), _
                    Key:=CStr(arrWords(j))
                On Error GoTo ErrHandler
            Next j
        End If
    Next i
    If col.Count > 0 Then
        ' Sort the results
        SortCollection col
        ' Concatenate them
        For i = 1 To col.Count
            strResult = strResult & Separator & col(i)
        Next i
        ' Remove first separator
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIfsList = strResult
    Exit Function
ErrHandler:
    ConcatenateIfsList = CVErr(xlErrValue)
End Function

Sub SortCollection(col As Collection)
    Dim i As Long
    Dim j As Long
    Dim tmp As Variant
    For i = 1 To col.Count - 1
        For j = i + 1 To col.Count
            If col(j) < col(i) Then
                tmp = col(j)
                col.Remove Index:=j
                col.Add Item:=tmp, Key:=CStr(tmp), Before:=i
            End If
        Next j
    Next i
End Sub

Example of usage:

=ConcatenateIfsList(B1:B6,A1:A6,"=","London",", ")

This will concatenate the words found in B1:B6 with ", " if the corresponding cell in A1:A6 equals London:

S274.png

[pandrews]

Excellent! Thanks :)

Can it have multiple separators? (i.e. '/', '-' etc)

I note it will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep'

[HansV]

Multiple separators? What would you want to do with them?

And if you have "mary,lamb,sheep", specify "," (without a space after the comma) instead of ", " as separator.

[pandrews]

Just to make it future proof - I may come across with Mary/Lamb/Sheep etc

It will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep' as it will process:

mary,lamb,sheep
mary, lamb, sheep

as

lamb, mary, mary,lamb,sheep, sheep

[HansV]

If you change the lines

Code: Select all

                col.Add Item:=arrWords(j), _
                    Key:=CStr(arrWords(j))

to

Code: Select all

                col.Add Item:=Trim(arrWords(j)), _
                    Key:=Trim(arrWords(j))

You can then specify "," as separator. The code will handle both "mary, lamb" and "mary,lamb" correctly.

To handle several separators, it's probably easier to replace "/", "-" etc. with "," in the concatenate range itself.

[DuncanDT]

Hey HansV - looks like you're a bit of a guru, and are incredibly generous with your time!

My company insists on using Google Sheets...not excel. Do you know how to transcribe your code (the one I've pasted below, which is amazing) into Google Sheets speak ("Google Apps Script", I think). Or do you know where I can find someone that can do this? Thank you so much mate, you're amazing.

Code: Select all

Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, August 2014
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim strResult As String
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n < 3 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    If n Mod 3 = 0 Then
        ' Separator specified explicitly
        Separator = Criteria(n)
    Else
        ' Use default separator
        Separator = ","
    End If
    ' Loop through the cells of the concatenate range
    For i = 1 To ConcatenateRange.Count
        ' Start by assuming that we have a match
        f = True
        ' Loop through the conditions
        For c = 0 To n - 1 Step 3
            ' Does cell in criteria range match the condition?
            Select Case Criteria(c + 1)
                Case "<="
                    If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<"
                    If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">="
                    If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">"
                    If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<>"
                    If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case Else
                    If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
            End Select
        Next c
        ' Were all criteria satisfied?
        If f Then
            ' If so, add separator and value to result
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        ' Remove first separator
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIfs = strResult
    Exit Function
ErrHandler:
    ConcatenateIfs = CVErr(xlErrValue)
End Function 

[HansV]

Welcome to Eileen's Lounge!

I have no experience with Google Apps Script. You might try the Google Apps Script Communities.

[DuncanDT]

Thanks Hans, really appreciate the speedy reply. All the best!

[blindzero678]

Hi Hans,

I envy your excel skills!

I was using the version of this function posted below and it was working fine until I ran another macro. Now I get a #Value error every time i try to use the concatenateifs function (in new cells and in existing cells with the function already applied). Any suggestions on what I could be doing wrong?

Thanks so much for your time!

Code: Select all

Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, August 2014
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim strResult As String
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n < 3 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    If n Mod 3 = 0 Then
        ' Separator specified explicitly
        Separator = Criteria(n)
    Else
        ' Use default separator
        Separator = ","
    End If
    ' Loop through the cells of the concatenate range
    For i = 1 To ConcatenateRange.Count
        ' Start by assuming that we have a match
        f = True
        ' Loop through the conditions
        For c = 0 To n - 1 Step 3
            ' Does cell in criteria range match the condition?
            Select Case Criteria(c + 1)
                Case "<="
                    If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<"
                    If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">="
                    If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case ">"
                    If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case "<>"
                    If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
                Case Else
                    If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
                        f = False
                        Exit For
                    End If
            End Select
        Next c
        ' Were all criteria satisfied?
        If f Then
            ' If so, add separator and value to result
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        ' Remove first separator
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIfs = strResult
    Exit Function
ErrHandler:
    ConcatenateIfs = CVErr(xlErrValue)
End Function

[Rudi]

Hi blindzero678,

Welcome to Eileen's Lounge.

Hans will need to verify my suggestion, but it seems the origninal function had the condition:

n = UBound(Criteria)
If n < 2 Then...

...yet in your posted syntax you have:

If n < 3 Then...

Changing it back to n < 2 makes the function work as expected (at least in my tests).

[HansV]

Rudi is correct that it should be

If n < 2 Then

(I think I made this mistake myself in one of the versions). But that shouldn't cause formulas that worked before to suddenly return #Value. The problem would only occur if you specify only one condition and omit the separator, otherwise it shouldn't make a difference.
If you could attach a sample workbook that demonstrates the problem, without sensitive information, we could investigate the problem directly.