| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple sets words replace | New Users to Excel | |||
| Count fowmula that will look for multiple words | Excel Discussion (Misc queries) | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| Search for multiple words | Excel Discussion (Misc queries) | |||
| IF Statement equalling multiple words. Please Help | Excel Discussion (Misc queries) | |||
Linear Mode
