Can this be done

[HansV]

Change the line

Code: Select all

    For i = 1 To ConcatenateRange.Count

to

Code: Select all

    For i = 1 To ConcatenateRange.Rows.Count

[EXCELM]

Hi Hans,

I'm joining the lot of those who asked you for explanations / customisations on this concatenateifs, given your endless availability!

I'm trying to apply your concatenateifs (i.e. multiple conditions) with operators (post 16 at page 3). The only result I get is an empty cell, and I know there are lines in the sheet that satisfy both criteria.

What I'm really trying to do is to have 2 conditions linked by and AND (for which your concatenateifs UDF would work), with a twist: one of these 2 condition must allow multiple criteria linked by an OR. Similar to a =SUM(COUNTIFS(range,{"red","blue","green"})) where the criteria is either Red, Blue or Green.

As your concatenateifs wasn't written to account for this twist, i thought of turning things around: out of 5 possible criteria for the 1st condition in 1 column ("Story", "Task", "Improvement", "Sub-task", "Bug") and 1 criteria for the 2nd condition in another column ("Sprint 8"), rather than asking to return those who satisfy

("Story" OR "Task" OR "Improvement" OR "Sub-task") AND ("Sprint 8")

i'm asking:

(Anything but "Bug") AND ("Sprint 8")

where "anything" is the operator "<>". Result ... an empty cell.


Question: what am I doing wrong? I'm attaching the XLS showing the empty cell.


Could I possibly be really cheeky and ask you for a revised UDF to do that OR / AND, if it's not too much work?

Thank you either ways, for these 7 pages of help!

[HansV]

Welcome to Eileen's Lounge!

I will look at your request.

[HansV]

About your first question: you forgot the "=" operator for the second condition:

=IF($J$8-$G11>=1,concatenateifs($B$3:$B$1000,$C$3:$C$1000,"<>","Bug",$D$3:$D$1000,"=","Sprint "&$J$8-$G11,", "), "")

[HansV]

I fear that modifying the ConcatenateIfs function with operators to handle multiple OR-criteria for each range would be complicated; I'd rather not do that unless it is absolutely essential.

[EXCELM]

Thank you very much Hans - I just tried it and it works perfectly!
I hadn't specified the "=" operator as I understood Excel would assume it, but apparently if I specify the operator in the 1st condition I must also specify the operator in all the other conditions.
No need to modify the UDF then, but thanks for the availability.

Best.

[tabbymulla]

Thanks for this thread.

[hombredeacero]

i have data in a column in the mm/dd/yyyy format. when i use concatenateif, its output is a text string where the dates come in as text mm/d/yyyy. that change effects the way a program reads single day dates. i cannot merely use TEXT() to convert it. any ideas?

[HansV]

Welcome to Eileen's Lounge!

The following version of ConcatenateIf will concatenate the dates the way they are displayed in the column:

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
    On Error GoTo ErrHandler
    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
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Text
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

[hombredeacero]

So changed to text from values. Great, thank you.