CODEWITHSUNDEEP
excelexcel-formula
William Humphries
William Humphries

Reputation: 568

How to Return a Comma Separated Value List to single Excel Cell based on Condition from Range

I have a table I need to look through and return the first row where every corresponding value in the second row is "Yes"

In my case this would mean: "TA", and "RA"

+-----+----+-----+
| TA  | SA | RA  |
+-----+----+-----+
| Yes | No | Yes |
+-----+----+-----+

Is there a way to combine these values into a single Excel cell separated by commas?

So that a single cell will contain "TA, RA"

What formula I've used is store all values into an array and then have that array write to adjacent cells all values with yes and then combine them with substitute function.

=IFERROR(INDEX($A$1:$C$1,1,IFERROR(SMALL(IF($A1:$A3="Yes",COLUMN($A1:$A3)),ROW(1:1)), "")), "")

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1)," ",", ")

This will turn:

+----+----+
| TA | RA |
+----+----+

into:

+--------+
| TA, RA |
+--------+

Is there a quicker/cleaner way to build this function to return all the values that match to "Yes" into a single cell separated by commas?

Upvotes: 0

Views: 2146

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.

=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

enter image description here

Upvotes: 3

dwirony
dwirony

Reputation: 5450

Are you against a custom function?

Function LISTTHEYESES(valuerange As Range)

Dim cell As Range, resultstring As String

For Each cell In valuerange
    If cell.Offset(1, 0).Value = "Yes" Then
        If resultstring <> "" Then
            resultstring = resultstring & ", " & cell.Value
        Else
            resultstring = cell.Value
        End If
    End If
Next cell

LISTTHEYESES = resultstring

End Function

img1

Upvotes: 1

Related Questions