Can this be done

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Welcome to Eileen's Lounge!

You can specify CHAR(10) as the very last argument of ConcatenateIfs. In one of the posts above, I used the example

=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,", ")

To separate the entries by new lines, change it to

=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,CHAR(10))

You should turn on Wrap Text for the cell with the formula.
Best wishes,
Hans

voff86
NewLounger
Posts: 2
Joined: 20 May 2015, 19:52

Re: Can this be done

Post by voff86 »

Wow, works like a charm Hans ! I've been strugguling for days trying to insert the vbnewline.

Thank you very much !

Voff

virgo71il
NewLounger
Posts: 5
Joined: 29 Sep 2015, 22:03

Re: Can this be done

Post by virgo71il »

HansV - I hope your still monitoring this posting. I was wondering if ConcatenateIfs works with named ranges?

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Can this be done

Post by Rudi »

Hi Virgo,

Welcome to Eileen's Lounge.

Hans is an Admin on this site and frequents it regularly still.

The ConcatenateIfs() function can take range names. Excel (AFAIK) does not differentiate between range names and rage references; it is the same thing to Excel.
Attached is an example of ConcatenateIfs() with a range name in the first argument (Name range called DataA).
concatenateifsquery for lounge.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Welcome to Eileen's Lounge!

I'm travelling at the moment and I can't check this until next week. But I see that Rudi has already tested it for you (thanks, Rudi).
Best wishes,
Hans

virgo71il
NewLounger
Posts: 5
Joined: 29 Sep 2015, 22:03

Re: Can this be done

Post by virgo71il »

Thanks Rudy. I was also wondering if the sorting capability could be added to the ConcatenateIf() function as well.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

The version that I posted in this thread and that Rudi used in his sample workbook already sorts the result. For example, in cell D2 on Query Sheet, you see

1,9,18,27,35

The concatenated values are sorted in ascending order, although the DataA range on Data Sheet is not sorted.

If you want to sort by other criteria, the code would have to be customized. If you indicate what you want, it may well be possible.
Best wishes,
Hans

Orayb
NewLounger
Posts: 3
Joined: 08 Oct 2015, 16:05

Re: Can this be done

Post by Orayb »

I used the code provided to create the concatenateif formula, however, it gives me the #Name# result.
would you please check it for me in the Linked template tab Cell G3 and let me know what is wrong in the VBA or in the formula


your help will be highly appreciated :)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Welcome to Eileen's Lounge!

Did you intend to attach a sample workbook?
Best wishes,
Hans

Orayb
NewLounger
Posts: 3
Joined: 08 Oct 2015, 16:05

Re: Can this be done

Post by Orayb »

my Appologies as i thought that i did attach the file
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

The cause of the problem is that you have given the function and the module that contains it the same name. This confuses Excel. If you give the module another name, for example basConcatenateIf, the function will work. You will have to recalculate the formula once, for example by selecting the cell with the formula, pressing F2 then enter.
Best wishes,
Hans

Orayb
NewLounger
Posts: 3
Joined: 08 Oct 2015, 16:05

Re: Can this be done

Post by Orayb »

Thanks a lot for the help as it works now, only thing i had to change is to make sure that the criteria cell needs to be in c=values if numbers due to the way it was recorded in the look up range.
one more question though is that when I do any change in the formula in the file that was converted to macro after this VBA, the file get stock and keeps working forever, any suggestion for how should i fix this issue?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

The VBA function has to perform a large amount of calculations, so if you have many formulas using this function, recalculating will cost a lot of time; that is unavoidable.
Best wishes,
Hans

virgo71il
NewLounger
Posts: 5
Joined: 29 Sep 2015, 22:03

Re: Can this be done

Post by virgo71il »

Hans/Rudy - I can't figure out what I'm doing wrong when trying to use the ConcatenateIfs function. I can use the ConcatenateIf function with no problem, but I need to use the ConcatenateIfs in order to sort my results. Please see the attached images. Any of your help is greatly appreciated.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Can you tell us which version of ConcatenateIfs you are using? I posted multiple versions in this thread, with different syntax.
Best wishes,
Hans

virgo71il
NewLounger
Posts: 5
Joined: 29 Sep 2015, 22:03

Re: Can this be done

Post by virgo71il »

Sorry, that would've help huh? Please see below:

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
        Dim col As Collection
        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
        ' 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
                ' If so, add value to collection, if it has not been added yet
                On Error Resume Next
                col.Add Item:=ConcatenateRange.Cells(i).Value, _
                    Key:=CStr(ConcatenateRange.Cells(i).Value)
                On Error GoTo ErrHandler
            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
        ConcatenateIfs = strResult
        Exit Function
ErrHandler:
        ConcatenateIfs = 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
Last edited by HansV on 21 Oct 2015, 14:55, edited 1 time in total.
Reason: to add [code] ... [/code] tags around code

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Thanks. The syntax for this version is as follows:

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

Parts in square brackets [ ] are optional.

RangeToConcatenate is the range with text values to be concatenated.
CriteriaRange1 is a range containing cells to test for a condition; this range must be the same size as RangeToConcatenate.
Operator1 can be one of the following: "<=", "<", ">=", ">", "<>", or "=". If left empty, "=" is assumed.
Condition1 must evaluate to a single value; each cell in CriteriaRange1 will be compared to this value according to Operator1.
You can optionally specify similar triples CriteriaRange2, Operator2, Condition2 etc.
Values from RangeToConcatenate that meet the conditions will be concatenated using the Separator string; if Separator is omitted, ", " will be used.

Example: you want to concatenate values from A1:A20 if the corresponding value in B1:B20 is greater than or equal to the value in D1 and less than or equal to the value in D2, and use "/" as separator:

=ConcatenateIfs(A1:A20, B1:B20, ">=", D1, B1:B20, "<=", D2, "/")

Instead of A1:A20, B1:B20, D1 and D2, you can use named ranges.
Best wishes,
Hans

virgo71il
NewLounger
Posts: 5
Joined: 29 Sep 2015, 22:03

Re: Can this be done

Post by virgo71il »

Hans - Thank you. I finally was able to get the ConcatenateIfs to work, but my results aren't sorting. Any suggestions?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

The screenshot shows that the values in the ConcatenateRange are text values (because they are enclosed in quotes), so they are sorted in alphanumeric order ("10" comes before "2" since the first character in "10" comes before "2").
If you convert the values in ConcatenateRange to numbers, they should be sorted in numeric order.
Best wishes,
Hans

diedrich08
NewLounger
Posts: 20
Joined: 16 May 2016, 18:31

Re: Can this be done

Post by diedrich08 »

Hello,
Relative to all on here, I am a novice in excel. I have attempted to use the concatentateifs function in excel, but when I do so, I get the #NAME error.

I have done the following:
1) gone into visual basic and inserted the following module:

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 < 2 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    If n Mod 2 = 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 2
            ' Does cell in criteria range match the condition?
            If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
                ' If not, we don't have a match
                f = False
                Exit For
            End If
        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
2) Following this, I clicked "save" and exited visual basic

When I try to use the concatentateifs function, I get this error. Can someone please tell me what I am doing wrong?

FYI - I am using a work computer and there may be restrictions. I am able to record macro's without issues though.

Thanks for the help everyone!

Sincerely,

VBA novice
Last edited by Rudi on 16 May 2016, 19:10, edited 1 time in total.
Reason: Edited to add [code]...[/code] tags and indenting...