Hi Dave,
thanks your help.It's work. thks

"Dave Peterson" wrote:

First, except for the =counta() portion, all those range references have to be
single cells (not like H42:af42).

Second, you're limited in xl2003 and below to 1024 characters in a formula (when
measured in R1C1 reference style). So with 47 fields, you're going to be close
(or over) that limit.

You could use multiple cells and do smaller groups at a time and then combine
them into a total result cell -- or you could use a macro:

Option Explicit
Function myConCat(TopRow As Range, ThisRow As Range)

Dim myStr As String
Dim iCtr As Long

If TopRow.Columns.Count < ThisRow.Columns.Count _
Or TopRow.Areas.Count < 1 _
Or TopRow.Rows.Count < 1 _
Or ThisRow.Areas.Count < 1 _
Or ThisRow.Rows.Count < 1 Then
myConCat = CVErr(xlErrRef)
End If

If Application.CountA(ThisRow) = ThisRow.Cells.Count Then
myStr = "All"
Else
myStr = ""
For iCtr = 1 To ThisRow.Cells.Count
If IsEmpty(ThisRow.Cells(1, iCtr).Value) Then
'skip it
Else
myStr = myStr & "_" & TopRow.Cells(1, iCtr).Value
End If
Next iCtr
If myStr < "" Then
myStr = Mid(myStr, 2)
End If
End If

myConCat = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myconcat($A$1:$D$1,A2:D2)

freefree wrote:

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


--

Dave Peterson