| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
location
hi ...plz help
given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
I assume by location you mean the cell address:
="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- Biff Microsoft Excel MVP "pierre" wrote in message ... hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
Ok, if you're going to move things around the only way to get the formula to
work correctly is to give the range a defined name. C5:C7 = defined named range = rng D5 = lookup value Enter this formula in E4: =COUNTIF(rng,D5) Enter this array formula** in E5: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
u are a real master.......
but please i need to know what follows : in this formula that you gave me : {=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") } first question : can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE THEN ?? please show it to me i cant do it second question : if i would like to make the lookup value : D5 and D6....i think that the formula will include TRANSPOSE like you taught me ....but how the formula will look like then ?? please show it to me third question: what does the "4" stands for ? THANKS FOR YOUR HELP REALLY APPRECIATED "T. Valko" wrote: Ok, if you're going to move things around the only way to get the formula to work correctly is to give the range a defined name. C5:C7 = defined named range = rng D5 = lookup value Enter this formula in E4: =COUNTIF(rng,D5) Enter this array formula** in E5: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
can we substitute : -MIN(ROW(rng))+1)
to the following : ROWS(rng) ??...... No, that won't work. second question : Change the formula in E4 to: =SUMPRODUCT(COUNTIF(rng,D5:D6)) Change the array formula** in D5 to: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") third question: what does the "4" stands for ? That's the argument that determines what reference style the result will be in. 4 means relative so the result is returned without $ signs. It's easier to read without the $ signs. -- Biff Microsoft Excel MVP "pierre" wrote in message ... u are a real master....... but please i need to know what follows : in this formula that you gave me : {=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") } first question : can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE THEN ?? please show it to me i cant do it second question : if i would like to make the lookup value : D5 and D6....i think that the formula will include TRANSPOSE like you taught me ....but how the formula will look like then ?? please show it to me third question: what does the "4" stands for ? THANKS FOR YOUR HELP REALLY APPRECIATED "T. Valko" wrote: Ok, if you're going to move things around the only way to get the formula to work correctly is to give the range a defined name. C5:C7 = defined named range = rng D5 = lookup value Enter this formula in E4: =COUNTIF(rng,D5) Enter this array formula** in E5: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
One last question :
regarding this formula : {=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMAL L(IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") } CAN WE WRITE IN ANOTHER WAY IN ORDER TO BE SHORTER ??? especially this part : ROW(rng)-MIN(ROW(rng))+1) THANKS AGAIN "T. Valko" wrote: can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??...... No, that won't work. second question : Change the formula in E4 to: =SUMPRODUCT(COUNTIF(rng,D5:D6)) Change the array formula** in D5 to: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") third question: what does the "4" stands for ? That's the argument that determines what reference style the result will be in. 4 means relative so the result is returned without $ signs. It's easier to read without the $ signs. -- Biff Microsoft Excel MVP "pierre" wrote in message ... u are a real master....... but please i need to know what follows : in this formula that you gave me : {=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") } first question : can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE THEN ?? please show it to me i cant do it second question : if i would like to make the lookup value : D5 and D6....i think that the formula will include TRANSPOSE like you taught me ....but how the formula will look like then ?? please show it to me third question: what does the "4" stands for ? THANKS FOR YOUR HELP REALLY APPRECIATED "T. Valko" wrote: Ok, if you're going to move things around the only way to get the formula to work correctly is to give the range a defined name. C5:C7 = defined named range = rng D5 = lookup value Enter this formula in E4: =COUNTIF(rng,D5) Enter this array formula** in E5: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
You seem to be overly concerned with:
ROW(rng)-MIN(ROW(rng))+1) You could put this portion in a separate cell and then refer to that cell: =MIN(ROW(rng))+1 You need to understand what this is doing. When you index a range the values of that range are in specific positions. The positions are numbered 1 through the total number of cells in the range. Your range is C5:C7. So: C5 = position 1 C6 = position 2 C7 = position 3 In order to get the correct result from the formula we need to tell the INDEX function we want the value located at position n. We do that using ROW(rng)-MIN(ROW(rng))+1). We have to convert the actual row numbers of "rng" to correspond to the position numbers of the indexed range. The actual row numbers of "rng" are 5,6,7. We need to convert those to 1,2,3. Here's how we do that: ROW(rng)-MIN(ROW(rng))+1) ROW(C5)-MIN(ROW(C5))+1 = 1 ROW(C6)-MIN(ROW(C5))+1 = 2 ROW(C7)-MIN(ROW(C5))+1 = 3 The *only* time you could replace ROW(rng)-MIN(ROW(rng))+1) with just ROW(rng) is *if* the actual indexed range started in row 1. For example, if the actual indexed range was A1:A3. In this case the actual row numbers naturally correspond to the position numbers on the indexed range. A1 = row 1 = position 1 A2 = row 2 = position 2 A3 = row 3 = position 3 However, if the actual range was A1:A3 and you used just ROW(rng) and inserted a new row 1 the formula could return incorrect results because now the row numbers do not correspond to the position numbers of the indexed range. Using ROW(rng)-MIN(ROW(rng))+1) accounts for this. This is the most fool-proof method to convert the actual row numbers to the correct position numbers. exp101 -- Biff Microsoft Excel MVP "pierre" wrote in message ... One last question : regarding this formula : {=IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMAL L(IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") } CAN WE WRITE IN ANOTHER WAY IN ORDER TO BE SHORTER ??? especially this part : ROW(rng)-MIN(ROW(rng))+1) THANKS AGAIN "T. Valko" wrote: can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??...... No, that won't work. second question : Change the formula in E4 to: =SUMPRODUCT(COUNTIF(rng,D5:D6)) Change the array formula** in D5 to: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(ISNUMBER(MATCH(rng,D$5:D$6,0)),ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") third question: what does the "4" stands for ? That's the argument that determines what reference style the result will be in. 4 means relative so the result is returned without $ signs. It's easier to read without the $ signs. -- Biff Microsoft Excel MVP "pierre" wrote in message ... u are a real master....... but please i need to know what follows : in this formula that you gave me : {=IF(ROWS(E$5:E5)<=E$4;ADDRESS(INDEX(ROW(rng);SMAL L(IF(rng=D$5;ROW(rng)-MIN(ROW(rng))+1);ROWS(E$5:E5)));COLUMN(rng);4);"") } first question : can we substitute : -MIN(ROW(rng))+1) to the following : ROWS(rng) ??......AND HOW THE FORMULA WOULD BE THEN ?? please show it to me i cant do it second question : if i would like to make the lookup value : D5 and D6....i think that the formula will include TRANSPOSE like you taught me ....but how the formula will look like then ?? please show it to me third question: what does the "4" stands for ? THANKS FOR YOUR HELP REALLY APPRECIATED "T. Valko" wrote: Ok, if you're going to move things around the only way to get the formula to work correctly is to give the range a defined name. C5:C7 = defined named range = rng D5 = lookup value Enter this formula in E4: =COUNTIF(rng,D5) Enter this array formula** in E5: =IF(ROWS(E$5:E5)<=E$4,ADDRESS(INDEX(ROW(rng),SMALL (IF(rng=D$5,ROW(rng)-MIN(ROW(rng))+1),ROWS(E$5:E5))),COLUMN(rng),4),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... yes please i want *all* the locations listed... One more little thing...suppose that i have changed the given and i moved its location... i.e : from colomn C to column A (see below) : C A 5 tea 5 tea 6 tree 6 tree 7 oil 7 oil i have noticed that the the formula : ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) is giving me C1 although it should be A1....do you have any tips for that ?? THANK YOU "T. Valko" wrote: I assume by location you mean the cell address: ="C"&INDEX(ROW(C5:C7),MATCH(D5,C5:C7,0)) As for the precedence, the formula will *always* find the first instance. Do you want *all* the locations listed? -- hi ...plz help given : C 5 tea 6 tree 7 oil my search string is D5.... also i put my formula in E5 : =MATCH(D5;$C$5:$C$7;0) this mean that if i type the word "tree" in cell D5...the result will be =2 which means the second criteria NOW MY GOAL IS TO FIND A FORMULA WIHICH INDICATES ME THE LOCATION OF THE WORD "TREE".....PLZ HELP PS: IF THE GIVEN WOULD BE : C 5 tea 6 tree 7 tree HOW TO DEAL HERE WITH THE PRECEDENT PROCEDURE AND WHAT FORMULAS TO USE ??? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
THANKS for your time , your patience , and for these clear and important answers THANK YOU SIR....... |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
location
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "pierre" wrote in message ... THANKS for your time , your patience , and for these clear and important answers THANK YOU SIR....... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Location of AddIns | Excel Discussion (Misc queries) | |||
| Tab location | Excel Discussion (Misc queries) | |||
| Template location | Excel Discussion (Misc queries) | |||
| VBA Options Location | Setting up and Configuration of Excel | |||
| Cursor Location | Excel Discussion (Misc queries) | |||
Linear Mode
