Can this be done

[blindzero678]

Thanks Rudi and Hans! Changing the line to n < 2 worked perfectly.

One more question though - could you show me how to adjust the function to not display the delimiter for concatenated cells that are blank?

Thank you again!

[HansV]

Like this:

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 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
        If ConcatenateRange.Cells(i).Value <> "" Then
            ' 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
        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

[Nick-ZG]

Hello everyone, I have a little problem that I think you could help me with your skills, and it would take me ages to get it sorted. I was using an original ConcatenateIf function, with a single criteria, and it works great for my needs, but for one small detail. I have a pilot logbook and I want to create a monthly summaries by days and in one cell i want my "flight legs" to be displayed. All I want to do is add the final destination to it, which is always 2 columns to the right of a last Concatenate value from my B column.
This is the result of a normal ConcatenateIf function in B column on my PAY sheet:

From this data:


So for my 1st of July 2016 the desired output result should be LDZA-LSZH-LDZA-LDZD-LDPL. Is this possible?
Thank you for all your help in advance.

[HansV]

Welcome to Eileen's Lounge!

Apologies for the late reply, I was busy with other things yesterday.

You can use an array formula to get the destination of the last flight on the specified date. I'll assume that you have assigned the name ARR to the list of arrival places. The last destination is (confirmed with Ctrl+Shift+Enter):

=INDEX(ARR;MAX(IF(Date=DATE($B$3;$D$3;A5);ROW(Date)-ROW(INDEX(Date;1;1))+1)))

So the complete formula (also confirmed with Ctrl+Shift+Enter to make it an array formula) is

=ConcatenateIf(Date;DATE($B$3;$D$3;A5);DEP;"-")&"-"&INDEX(ARR;MAX(IF(Date=DATE($B$3;$D$3;A5);ROW(Date)-ROW(INDEX(Date;1;1))+1)))

[Nick-ZG]

Thanks so much It seams great, but i think it needs another if because it is now adding last values where it is not supposed to.
This is with array

And without

It adds unwanted values to dates that do not have any flights associated or -Arrival
Am I doing something wrong?

[HansV]

I hadn't taken into account that there might be dates without flights. The formula becomes more complicated:

=ConcatenateIf(Date;DATE($B$3;$D$3;A5);DEP;"-")&IF(ISNUMBER(MATCH(DATE($B$3;$D$3;A5);Date;0));"-"&INDEX(ARR;MAX(IF(Date=DATE($B$3;$D$3;A5);ROW(Date)-ROW(INDEX(Date;1;1))+1)));"")

As always, confirm with Ctrl+Shift+Enter.

[Nick-ZG]

Man you are awesome, thank you so much. I might have some more questions on some other aspects I will have to add, but till that time thank you so much :)

[aufblicken]

Hans,

At the very beginning of this thread (08 Feb 2013, 08:21), you wrote a perfect user-defined ConcatenateIF function, which evolved into an IFS function with the added feature of eliminating repeats in the results.

I hope you're not tired of this subject or the fame, and I wondered if you could please provide a simple ConcatenateIF with systax that eliminates repeat returns?

And I agree with the user who said Microsoft should implement this and back-pay you for helping with this much needed gem!

Thanks,
aufblicken

[HansV]

Welcome to Eileen's Lounge!

Here is a version that returns unique results only:

Code: Select all

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    Dim dict As Object
    On Error GoTo ErrHandler
    Set dict = CreateObject("Scripting.Dictionary")
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            If ConcatenateRange.Cells(i).Value <> "" Then
                If Not dict.Exists(ConcatenateRange.Cells(i).Value) Then
                    dict.Add Key:=ConcatenateRange.Cells(i).Value, Item:=ConcatenateRange.Cells(i).Value
                End If
            End If
        End If
    Next i
    ConcatenateIf = Join(dict.keys, Separator)
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Usage is the same as the original version, e.g.

=ConcatenateIf(A1:A20,"USA",B1:B20)

will return a comma-separated list of unique values from B1:B20 for which the corresponding cell in A1:A20 equals "USA".

[aufblicken]

Thank you, Hans -- what a perfect solution for my needs. Very, very much appreciated!

[yashgee]

Hi Hans!

concactenateif is Exactly what I was looking for. But unfortunately can´t get it to work Always get a compile error:syntax error. Any ideas?

In the past, with some imported VBA modules, I have noticed that I had to replace the "," by ";" as in my PC, maybe owing to my regional settings, that's the only way it works. Avidly use the built in sumifs etc. But can´t understand where am going wrong on this one.

One more possibility that comes to mind is the fact that in office 365, "concat" replaces "concactenate". Can you help out please?

Thanks in advance,

Yash

[HansV]

Welcome to Eileen's Lounge!

VBA code uses US English settings, regardless of your regional settings, so the ConcatenateIf function should always work. But you do have to use ; instead of , in the cell formula, for example

=ConcatenateIf(A1:A20;"USA";B1:B20)

instead of

=ConcatenateIf(A1:A20,"USA",B1:B20)

CONCAT and TEXTJOIN are new functions with more functionality in Excel 365, but the CONCATENATE function still exists in Excel 365. The ConcatenateIf VBA function is independent of these built-in functions, however.

If you wish, you can attach a copy of your workbook without sensitive data to a reply. That way I and others can try to find out what's wrong.

[cmacinp]

Hello Hans,

Thank you for sharing you knowledge, I've been stuck on my file for months and even though I was able to find a solution, it is very long and freezes excel when it is a very long list. I finally found your post and the codes for ConcatenateIf and ConcatenateIfs and they are PERFECT for what I need. But I tried creating the user defined functions and its not working. I'm sorry to bother you, but this is the first time I've done it and even though I followed the instructions, it's not working.

Would you mind taking a look to my file and help me find what I'm not doing right? On the right (J1:M12) are the results that I'm trying to get with the functions

Your function will literally save me days of manual work. Thank you for sharing!

Claudia

[HansV]

Welcome to Eileen's Lounge!

There were two problems:

While copying the code into the Visual Basic Editor, non-breaking spaces should automatically have been replaced with ordinary spaces, but this failed for some reason. I have replaced the non-breaking spaces.

And you had given the modules the same name as the functions. That is not valid, so I have renamed the modules.

See the attached version.

concatenate.xlsm

[cmacinp]

Thank you so much Hans!

This was has been a really helpful function, I´m a fan.

All the best!

[HansV]

You're welcome. Feel free to come back if you have more questions.

[revelrob]

Hi Hans,
Thanks so much for this!
I was wondering if it was possible to have an OR condition for the concatenateifs.
In your example, you put the following:
=ConcatenateIfs(A1:A6, B1:B6, 2, C1:C6, "b")
If any of B2-B6 are equal to 2 AND C1-C6 are equal to b, the corresponding cell from A1-A6 is shown.
What I would love to accomplish is the same thing but instead of both ranges needing to be true, have either be true.

As in, If any of B2-B6 are equal to 2 OR C1-C6 are equal to b, the corresponding cell from A1-A6 is shown.

Is this possible?

Thanks!

[HansV]

Welcome to Eileen's Lounge!

Here is a version that uses Or. The function is named ConcatenateIfsOr:

Code: Select all

Function ConcatenateIfsOr(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, September 2017
    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 < 1 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 do not have a match
        f = False
        ' 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 so, we have a match
                f = True
                Exit For
            End If
        Next c
        ' Was at least one condition 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
    ConcatenateIfsOr = strResult
    Exit Function
ErrHandler:
    ConcatenateIfsOr = CVErr(xlErrValue)
End Function

Examples of usage:

To use the default separator:

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

To specify a custom separator:

=ConcatenateIfsOr(A1:A6, B1:B6, 2, C1:C6, "b", "|")

[revelrob]

You're Amazing Hans!
Thanks so much for the quick reply!

[chddiaz]

Hello,
I'm a novice to Excel and need to concatenate data from multiple cells in one column if data in cells of another column match. Here's an example:

Code: Select all

Original Data		Desired Data	
ID	 Code	      ID	 Code
100	99283	     100	99283, 99053
100	99053	     150	99285, 99053, j150, z160
150	99285	     200	993181, 99503, y33
150	99053	     500	2291, 2pa
150	j150
150	z160
200	993181
200	99503
200	y33
500	2291
500	2pa

I can use the a combination of IF and CONCATENATE, however, I get duplicate information in the cases where there is more than 2 matches in column A. In one spreadsheet alone, I have almost 100,000 rows to compare. Please help if you can.