Can this be done

[drknow1982]

Have attached a file...
it has a query sheet and data sheet... data sheet has columns labelled study id and no of cases
query sheet has the concatenated string from the concatenateifs query in the column labelled "Studies quoting".
I want to add the no of cases for each row when the "studies quoting" match the "study id"....

[HansV]

I'll have a look at it a bit later - I'll be away from my computer for a short while.

[HansV]

In B2 on the Query Sheet:

=SUMPRODUCT('Data Sheet'!$B$2:$B$45,--ISNUMBER(FIND(","&'Data Sheet'!$A$2:$A$45&",",","&A2&",")))

Fill down to the end of the data, for example by double-clicking the fill handle (the little black square in the lower right corner) of B2.

[drknow1982]

Thanks Hans...
It would be nice if you could explain the formula...... I understand what each of the individual function does.... but I don't understand what they are doing when you have nested them!!... especially the "--" added prior to ISNUMBER?

[drknow1982]

ah... i see... the "--" are converting the lookup from true/false into 1 or 0....
thats nice!!

[HansV]

FIND(...) either returns a number (if the Study ID is found in Studies Quoting) or an error (if the Study ID is not found).
ISNUMBER(FIND(...)) returns TRUE (if the Study ID is found in Studies Quoting) or FALSE (if the Study ID is not found).
We can't use this directly in SUMPRODUCT - it ignores TRUE/FALSE values, even though TRUE is stored inrernally as 1 and FALSE as 0.
-ISNUMBER(FIND(...)) forces Excel to treat the values as numbers: TRUE becomes -1 (because of the -) and FALSE becomes 0.
The second - in --ISNUMBER(FIND(...)) changes -1 to +1. As a result, each match counts as 1 and each non-match as 0.
This is multiplied with the values in the NO OF CASES column and summed.

[jschofer]

Hello and thank you for thge Concatenateifs function. I am struggling to use the criteria to check for text within the cell. as an example I would normally use the syntax

"*" & "test" & "*"

However the concatenate ifs function does not like that syntax.

The usage is that I am trying to concatenate all notes from a column if they are entered on a specific date.

Any help is appreciated

[HansV]

Welcome to Eileen's Lounge!

Neither of the versions of ConcatenateIfs that I posted support wildcards. But if you're looking for notes posted on a specific date, I don't think you need wildcards.
Let's say the notes are in B2:B100, the dates in A2:A100, and the specific date you're looking for is in cell K1.
You can then use the ConcatenateIf function from the first reply on page 1 of this thread, and use

=ConcatenateIf(A2:A100,K1,B2:B100,", ")

[jschofer]

Thank you Hans, I am looking to only concatenate notes that contain particular Text. Basically all notes that contain "text" for a particular date.

Ideally I would reference a header cell to indicate the text I am looking for.

Syntax would be something like "*" & "cell" & "*"

Again thank you

[HansV]

OK, here is a new version that supports wildcards for the "=" and "<>" operators.

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 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 & 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

Let's say the notes are in B2:B100, the dates in A2:A100, the specific text you're looking for is in L1 and the specific date you're looking for is in cell K1.

The formula would be like this

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

The first B2:B100 is the concatenate range. The second B2:B100 is the first criteria range, and "*"&L1&"*" is the first condition (with wildcards). A2:A100 is the second criteria range, and L1 is the second condition.

[jschofer]

Thank you, That seems to do it.

I appreciate all t he help.

Josh

[Harper]

Well, I'm surprised, I didn't know that it was possible to do. Excel always surprises me.

[Rudi]

Welcome to the lounge...

I have been using Excel for more than 2 decades and I still learn new things on a regular basis. Excel is a bottomless cup of coffee, literally!! Between the two powerhouses of Excel; it's logic formulas and macros, one can tweak Excel to do everything besides wash the dishes...

I have learned to hesitate before I say, no...Excel cannot do that!

[Harper]

Yes, I agree with you, but i'm feeling sad when these things happen, I think that i'm a novice user

[misslauryl]

I'm wondering if there is an easy adjustment to this:

Some of the cells that my ConcatenateIfs return are blank, so a few of the concatenations look like:

/ / / / / / / / #2much$W@G/ / / / a great personality and great fellow overall/ / / / / / / VERY helpful and informative/ / / / / / Not an a--hole/ / / / /

using "/ " as the separator.

I was thinking that I needed some thing that worked like an IsNotBlank

I tried adding criteria that work with countifs: [range], "*" or [range],"><"&"" where [range] was the same as [ConcatenateRange] but with each of those, the cells were just blank.

I'm not sure if there's a way to do this within the function or if it would have to be modified in VBA, in which case, I have no idea.

Thanks,
Laurel

[HansV]

Welcome to Eileen's Lounge!

You can add [range], "<>", ""

For example:

Code: Select all

=ConcatenateIfs(A2:A50, A2:A50, "<>", "")

[kapro888]

Hello,

I have found your concatenateifs formula to apply towards my spreadsheet. However, I have over 5000 rows of data to search through, and the workbook freezes up every time I attempt to apply it to all rows. Do you know of a way around this?

Thanks!

[HansV]

Welcome to Eileen's Lounge!

The ConcatenateIfs function has to perform a very large number of calculations, so slowness is unavoidable. If using it in cell formulas brings Excel to a halt, it's probably best not to use formulas, but to write a macro that populates cells with the result of ConcatenateIfs, and to only call this macro when you really need the values to be up-to-date.

[kapro888]

Thank you! I will try that!

[voff86]

Hi,

I'm using the ConcatenateIfs, and would like to add vbnewline separator in the cell instead of the Separator = ", "

How could I do this ?

Thanks,

Voff