CODEWITHSUNDEEP
excelexcel-formula
Michał Plich
Michał Plich

Reputation: 175

Excel formula to group values which meets specified creteria

See example below:

enter image description here

I'm looking for a single cell formula, which I can put in cell D2. I want this function to extract each value from Column B, where corresponding value in Column A equals "YES".

Is anything like this possible in cell formula, without using VBA?

Upvotes: 0

Views: 79

Answers (2)

Harun24hr
Harun24hr

Reputation: 36870

If you do not have TEXTJOIN() function then use below UDF.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Use as array formula in your case

=TEXTJOIN(";",TRUE,IF(A1:A6="YES",B1:B6,""))

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

enter image description here

The credit goes to Scott Craner from this post. TextJoin UDF For Excel 2013

Upvotes: 0

ApplePie
ApplePie

Reputation: 8942

You can use array formulas, which essentially compute calculations in an array and can be aggregated. As JvdV mentioned, you can use TEXTJOIN.

=TEXTJOIN(";",TRUE,IF(A1:A6="YES",B1:B6,""))

To use an array formula, if you haven't before, you need to finish your formula by entering SHIFT+ENTER, otherwise it will be interpreted as a regular, non-array, formula.

Upvotes: 2

Related Questions