About Us

freefree

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
multiple sets words replace	dk	New Users to Excel	5	April 10th 08 12:20 AM
Count fowmula that will look for multiple words	japc90	Excel Discussion (Misc queries)	2	January 7th 08 11:59 AM
Index & Match functions - multiple criteria and multiple results	[email protected]	Excel Worksheet Functions	4	May 2nd 07 03:13 AM
Search for multiple words	Kassie	Excel Discussion (Misc queries)	0	March 25th 05 05:21 PM
IF Statement equalling multiple words. Please Help	Phillip Vong	Excel Discussion (Misc queries)	4	March 18th 05 12:43 AM

Menu

About Us