CONCATENATE with multiple IFS

[mrnarley]

Hello,

The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS? Thanks!

=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)

 

[mrnarley]

The spreadsheet I'm querying is called "Data". Data!B:B is the column that contains the names I'd like to CONCATENATE into one cell on my worksheet. The criteria I'm using is as follows:

On the worksheet, B1 has the names of regions. There are several possibilities to choose from, those being BOS, CIN, CHI, and so on. There is also an "All" selection, in which case the formula should then include all of the regions for the query. The reference data for this section is stored in Data!A:A.

B2 contains several project types. There are several possibilities to choose from, including "small", "med", "large" and "All". Same rules apply for selecting "All". The reference data for this section is stored in Data!C:C.

B3 contains numbers for the projects. Possible selctions are 1,2,3 and "All". Same rules apply for slecting "All"; every record with a number will be included in the query results.

W5 and X5 contain dates. I'm looking for projects where the completion date falls between these two dates. The completions dates are stored in Data!E:E.

The end game being that any records that match all of the criteria have their names (stored in in Data!B:B) returned in a single cell on my worksheet, with a comma and space between each of the names that are returned to the cell.

Clear as mud, right? Thanks for your help.

 

[mikerickson]

I'm at work, but I'll try to get something together for you when I get off.
I think that substituting COUNTIFS for COUNTIF in the UDF's test section should make it possible to use your current COUNTIFS arguments.

 

[mrnarley]

I certainly do appreciate it!

 

[mikerickson]

I looked at the Help system description of COUNTIFS and SUMIFS at my works 2007 and have come up with this UDF that (I think) emulates their syntax with concatenation.

Concat2Ifs will return a string, the concatenation of the values in stringsRange when the matching cells in compareRange1 meet Criteria1 and the cells in compareRange2 meet Criteria2, etc.. A Delimiter can be specified and if the user wants no duplicates in the result string, NoDuplicates should be True.

compareRange2, Criteria2, compareRange3,...,Criteria5 and NoDuplicates are optional.

For your problem, it looks like putting this formula in a cell would be close to what you want.

=Concat5Ifs(Data!B:B,", ", Data!E:E,">"&W5, Data!E:E,"<"&X5, Data!A:A,B1, Data!C:C,B2)

I'm not sure where you store the Project numbers, but the above formula doesn't use range/criteria #5 so that can be added if needed.

I hope this helps

Function Concat5Ifs(ByVal stringsRange As Range, Delimiter As String, _
                            ByVal compareRange1 As Range, ByVal Criteria1 As Variant, _
                            Optional ByVal compareRange2 As Range, Optional ByVal Criteria2 As Variant, _
                            Optional ByVal compareRange3 As Range, Optional ByVal Criteria3 As Variant, _
                            Optional ByVal compareRange4 As Range, Optional ByVal Criteria4 As Variant, _
                            Optional ByVal compareRange5 As Range, Optional ByVal Criteria5 As Variant, _
                            Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange1.Parent
       Set compareRange1 = Application.Intersect(compareRange1, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange1 Is Nothing Then Exit Function
    If compareRange2 Is Nothing Then
        Set compareRange2 = compareRange1
        Criteria2 = Criteria1
    End If
    If compareRange3 Is Nothing Then
        Set compareRange3 = compareRange1
        Criteria3 = Criteria1
    End If
    If compareRange4 Is Nothing Then
        Set compareRange4 = compareRange1
        Criteria4 = Criteria1
    End If
    If compareRange5 Is Nothing Then
        Set compareRange5 = compareRange1
        Criteria5 = Criteria1
    End If
    Set stringsRange = compareRange1.Offset(stringsRange.Row - compareRange1.Row, _
                                                stringsRange.Column - compareRange1.Column)
    Set compareRange2 = compareRange1.Offset(compareRange2.Row - compareRange1.Row, _
                                                compareRange2.Column - compareRange1.Column)
    
        For i = 1 To compareRange1.Rows.Count
            For j = 1 To compareRange1.Columns.Count
                If (Application.CountIf(compareRange1.Cells(i, j), Criteria1) = 1) _
                    And (Application.CountIf(compareRange2.Cells(i, j), Criteria2) = 1) _
                    And (Application.CountIf(compareRange3.Cells(i, j), Criteria3) = 1) _
                    And (Application.CountIf(compareRange4.Cells(i, j), Criteria4) = 1) _
                    And (Application.CountIf(compareRange5.Cells(i, j), Criteria5) = 1) Then
                    If InStr(Concat5Ifs, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        Concat5Ifs = Concat5Ifs & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        Concat5Ifs = Mid(Concat5Ifs, Len(Delimiter) + 1)
End Function

This UDF does not require Excel2007.
(my next step, allow for unlimited number of compareRanges/criteria)
Thanks for the impulse to improve my UDF!

 

[mrnarley]

You're thanking me? Oh no. Thank you!

Sorry about leaving out that last part. The numbers are stored in D:D.

I took your UDF and created a module. The formula works for the most part. Here's what it looks like now:

=Concat5Ifs(Data!B:B,", ", Data!E:E,">"&V5, Data!E:E,"<"&W5, Data!A:A,B1, Data!C:C,B2, Data!D:D,B3)

Only problem is, it doesn't return any results when I select "All" in any of the drop down boxes in B1, B2, and B3. I'm not slick enough to change the code to include all records when All is selected. Is there a way to do that?

 

[mikerickson]

This will concatenate all of column A's entries if "All" is in any of B1, B2, or B3 and will return your formula otherwise.

=IF(OR(B1="All",B2="All",B3="All"), _
      Concat5Ifs(Data!B:B,", ",Data!B:B,"<>"), _
       Concat5Ifs(Data!B:B,", ", Data!E:E,">"&V5, Data!E:E,"<"&W5, Data!A:A,B1, Data!C:C,B2, Data!D:D,B3))

 

[mrnarley]

I would amend your existing code with what you've added in this last post? I'm not sure how to do that. Would you mind pointing me in the right direction? Sorry, I'm just not that advanced yet...but some day...

 

[mikerickson]

No, what I just posted was a spreadsheet formula. It should be put in a cell.
(I put it in the code tags to make it more readable. I should have emphasised that.)

It is a formula that uses the UDF, together with IF, to produce the strings you want.

One nice thing about UDF's is that basic Excel (i.e. non-VBA) techniques can be used to modify the results.

 

[mrnarley]

Oh man! It's soooo close to what I need! Here's what I inserted in the cell:

=IF(OR(B1="All",B2="All",B3="All"),Concat5Ifs(Data!B:B,", ",Data!B:B,"<>"),Concat5Ifs(Data!B:B,", ", Data!E:E,">"&V5, Data!E:E,"<"&W5, Data!A:A,B1, Data!C:C,B2, Data!D:D,B3))

Two issues:

1. The results include the column header label for B:B.

2. If I choose B1=CHI, B2=BIG, and B3=All, I would expect to get all projects in Chicago that are big and are assigned any of the 3 numbers. Instead I'm getting all projects regardless of where they are or what size they may be. This is true if I select "All" in any of the dropdown boxes in B1, B2 and B3. Weird. Any ideas?

I can't tell you how much I appreciaite your help. Almost there!

 

[mikerickson]

I think this will do it

=Concat5Ifs(Data!B2,", ", Data!E:E,">"&V5, Data!E:E,"<"&W5, Data!A:A,IF(B1="All","<>",B1), Data!C:C,IF(B2="All","<>",B2), Data!D:D,IF(B3="All","<>",B3)))