Hi Dave,
for below scenario can do it....but when i copy your answer to put on the
more column error came out as below:

=IF(COUNTA(H139:BB139)=47,"All", SUBSTITUTE(TRIM(IF(H99="","",H$42:AF$42&"
")&IF(AG99="","",AG$42:AM$42&" ")&IF(AN99="","",AN$42:BA$42&"
")&IF(BB99="","",BB$42&" "))," ","_"))

"Dave Peterson" wrote:

As long as those names in row 1 don't have any space characters in them:

=IF(COUNTA(A2:D2)=4,"All",
SUBSTITUTE(TRIM(IF(A2="","",A$1&" ")
&IF(B2="","",B$1&" ")
&IF(C2="","",C$1&" ")
&IF(D2="","",D$1&" "))," ","_"))

freefree wrote:

To Anyone:
how to use If function to do below scenario:-

susie lucy eric henry result
1 5 3 4 All
2 7 susie_henry
9 8 lucy_eric
7 5 eric_henry
5 5 susie_eric
5 8 susie_lucy
2 henry
3 eric
9 lucy
4 susie
4 6 lucy_henry

apprieciated your help. thks

--

Dave Peterson