CONCATENATE WITH CRITERIA
 

I want to concatenate the text in different row but exclude a particular
criteria if it comes up. See below example:

Different Scenarios:
A B C D E
1 undecided John John undecided John
2 undecided Paul undecided Paul Paul
3 undecided undecided undecided undecided Lily

RESULTS: undecided John, Paul John Paul John, Paul, Lily

Basically, I only want the "undecided" shows up only if all three options
are undecided; otherwise only give me the name(s) that is/are suggested.

Thanks.

--
M&M

CONCATENATE WITH CRITERIA
 

If all those names are single words:

=IF(COUNTIF(A1:A3,"undecided")=3,"undecided",
SUBSTITUTE(TRIM(SUBSTITUTE(A1&" "&A2&" "&A3,"undecided"," "))," ",", "))



M&M wrote:

I want to concatenate the text in different row but exclude a particular
criteria if it comes up. See below example:

Different Scenarios:
A B C D E
1 undecided John John undecided John
2 undecided Paul undecided Paul Paul
3 undecided undecided undecided undecided Lily

RESULTS: undecided John, Paul John Paul John, Paul, Lily

Basically, I only want the "undecided" shows up only if all three options
are undecided; otherwise only give me the name(s) that is/are suggested.

Thanks.

--
M&M

--

Dave Peterson

CONCATENATE WITH CRITERIA
 

Try
=IF(AND(A1="undecided",A2="undecided",A3="undecide d"),"Undecided",IF(A1="undecided","",A1&", ")&IF(A2="undecided","",A2&", ")&IF(A3="undecided","",A3))

Hope you can live with an extra comma at the end if third row is
'undecided'...

"M&M" wrote:

I want to concatenate the text in different row but exclude a particular
criteria if it comes up. See below example:

Different Scenarios:
A B C D E
1 undecided John John undecided John
2 undecided Paul undecided Paul Paul
3 undecided undecided undecided undecided Lily

RESULTS: undecided John, Paul John Paul John, Paul, Lily

Basically, I only want the "undecided" shows up only if all three options
are undecided; otherwise only give me the name(s) that is/are suggested.

Thanks.

--
M&M