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 here:
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
2)
