ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   If Functions for multiple words (https://www.excelbanter.com/excel-worksheet-functions/206209-if-functions-multiple-words.html)

freefree

If Functions for multiple words
 
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

NoodNutt

If Functions for multiple words
 
G'day

You're gunna have to be a bit more specific on what it is you are trying to
achieve.

A. Is it a constant or variable number that name have
B. Is it a score that each gains
C. A ranking

There are many ways this can be interpretted by those who don't quite fathom
what you ultimately are hoping to see.

breakdown the question down and re-post it and we'll try to help with you.

HTH
Regards
Mark



Dave Peterson

If Functions for multiple words
 
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

Ken Johnson

If Functions for multiple words
 
On Oct 14, 12:34*pm, 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


One way...

=IF(COUNTBLANK(A2:D2)=4,"",LEFT(IF(ISNUMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""),LEN(IF(ISN UMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""))-1))

Ken Johnson

freefree

If Functions for multiple words
 
Hi NoodNutt,
that is the numberic or value under each person. which is the number at
column in that person the result column will shown the name of the person
etc:susie_lucy or ....

"NoodNutt" wrote:

G'day

You're gunna have to be a bit more specific on what it is you are trying to
achieve.

A. Is it a constant or variable number that name have
B. Is it a score that each gains
C. A ranking

There are many ways this can be interpretted by those who don't quite fathom
what you ultimately are hoping to see.

breakdown the question down and re-post it and we'll try to help with you.

HTH
Regards
Mark




freefree

If Functions for multiple words
 
Hi Ken,
that result is came out like 1_5_3_4. i want the result is come out like
test "All',"susie_lucy"...

"Ken Johnson" wrote:

On Oct 14, 12:34 pm, 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


One way...

=IF(COUNTBLANK(A2:D2)=4,"",LEFT(IF(ISNUMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""),LEN(IF(ISN UMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""))-1))

Ken Johnson


freefree

If Functions for multiple words
 
Hi Dave,
your answer is helpped. thks a lot.

thanks to everyone which is reply to my msg. thks...

"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


freefree

If Functions for multiple words
 
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


Teethless mama

If Functions for multiple words
 
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/

....then use this formula

=IF(COUNT(A2:D2)=4,"All",SUBSTITUTE(TRIM(MCONCAT(I F(A2:D2<"",$A$1:$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

If Functions for multiple words
 
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

freefree

If Functions for multiple words
 
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



All times are GMT +1. The time now is 02:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com