Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Max asked me to re-post this in order to get more visibility.
Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Okay,
It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Thanks Bob. I am getting results but only in reference to colmn C and E.
Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Opps I meant colmn "D" is left out.
"Luke" wrote: Thanks Bob. I am getting results but only in reference to colmn C and E. Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Luke, you got a very nice question. Thanks for the eye-opener.
Actually I copy and modify your previous post and place it as "Array Help Part 2" and I find a very unusual static results on the formula been tried by Max and Biff, I hope the writer can really define the extents of these "function grabbing formula" that may destabilize the If function's expected dynamic behaviour...Mr. Bob Phillips, I hope you realize what you have discussed on the thread of TryHarder in respect to your longtime supposed dedication for ms excel. without further and thanks again Luke and Max. "Luke" wrote: Thanks Bob. I am getting results but only in reference to colmn C and E. Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Yes driller I seen your post. However over my head this all is, I have to
say that it is unique, the operations that can be performed by way of various functions. I'm a beginner for the past ten years :) but I learn something every time I'm here even if the outcome is not favorable. thanks for your input and do let us all know if you resolve the formula. regards, Luke "driller" wrote: Luke, you got a very nice question. Thanks for the eye-opener. Actually I copy and modify your previous post and place it as "Array Help Part 2" and I find a very unusual static results on the formula been tried by Max and Biff, I hope the writer can really define the extents of these "function grabbing formula" that may destabilize the If function's expected dynamic behaviour...Mr. Bob Phillips, I hope you realize what you have discussed on the thread of TryHarder in respect to your longtime supposed dedication for ms excel. without further and thanks again Luke and Max. "Luke" wrote: Thanks Bob. I am getting results but only in reference to colmn C and E. Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
see this acrobatic formula from ? post "Array Help Part 2"
=IF(AND(SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F6))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,G6))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,H6)))),IF(ISERROR((F6&G6&H 6)+0),F6&G6&H6,(F6&G6&H6)+0),"") when I grab =SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F6))) disecting it goes like this =Find($c$1:$e$3,F6) result is #VALUE! =ISNUMBER(FIND($C$1:$E$3,F6)) result is FALSE =SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F6))) result is a bloody 1 !!! amazing gosh I hope ms excel can explain this ! that's it for today! thanks Luke for the wide space...more power to real logic!!! "Luke" wrote: Yes driller I seen your post. However over my head this all is, I have to say that it is unique, the operations that can be performed by way of various functions. I'm a beginner for the past ten years :) but I learn something every time I'm here even if the outcome is not favorable. thanks for your input and do let us all know if you resolve the formula. regards, Luke "driller" wrote: Luke, you got a very nice question. Thanks for the eye-opener. Actually I copy and modify your previous post and place it as "Array Help Part 2" and I find a very unusual static results on the formula been tried by Max and Biff, I hope the writer can really define the extents of these "function grabbing formula" that may destabilize the If function's expected dynamic behaviour...Mr. Bob Phillips, I hope you realize what you have discussed on the thread of TryHarder in respect to your longtime supposed dedication for ms excel. without further and thanks again Luke and Max. "Luke" wrote: Thanks Bob. I am getting results but only in reference to colmn C and E. Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
here is the nightmare....
on C1:E3 i type a value of 1 on general number format and on F6:h6 i type 1 also. when I grab on B6 a part of the formula SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F6))) disecting it goes like this =Find($c$1:$e$3,F6) result is #VALUE! of course because its not a text =ISNUMBER(FIND($C$1:$E$3,F6)) result is FALSE =SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F6))) and the result is a shivering (NINE) 9 !!! a nine (9) against a single #VALUE! and under a single FALSE !!! thanks Luke. "Luke" wrote: Yes driller I seen your post. However over my head this all is, I have to say that it is unique, the operations that can be performed by way of various functions. I'm a beginner for the past ten years :) but I learn something every time I'm here even if the outcome is not favorable. thanks for your input and do let us all know if you resolve the formula. regards, Luke "driller" wrote: Luke, you got a very nice question. Thanks for the eye-opener. Actually I copy and modify your previous post and place it as "Array Help Part 2" and I find a very unusual static results on the formula been tried by Max and Biff, I hope the writer can really define the extents of these "function grabbing formula" that may destabilize the If function's expected dynamic behaviour...Mr. Bob Phillips, I hope you realize what you have discussed on the thread of TryHarder in respect to your longtime supposed dedication for ms excel. without further and thanks again Luke and Max. "Luke" wrote: Thanks Bob. I am getting results but only in reference to colmn C and E. Colmn B is left out inhibiting the results. The results show only B6=100 & B19=315. The rest are omitted. "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Okaay Bob!
I got it. You forgot to include colmn B. I think perhaps you knew I would get it:). Here it is In B2 Define Range name "lastX" Refer to as follows: =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MAX(IF($F$1:$F1="X",ROW($F$1:$ F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",ROW($F$1:$ F1)))+1) Then in B2 paste: =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and it works great! Thanks A bunch! Luke "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Luke,
I don't know what you have done it is not what I meant at all. You seem to be looking through all 3 columns individually, whereas I set a range to columns C:E, D gets included automatically. And if you notice, the first MAX statement subtracts 1, the second adds 1, this is so that we encompass the 3 rows, above, including, and below the X. Similarly, by INDEX into C and E we encompass columns C, D and E. My original as posted does work. Please try it again, you don't need the change that you made. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Okaay Bob! I got it. You forgot to include colmn B. I think perhaps you knew I would get it:). Here it is In B2 Define Range name "lastX" Refer to as follows: =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",RO W($F$1:$F1)))+1) Then in B2 paste: =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0) )),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(las tX,E2,0)))),(C2&D2&E2)+0,"")) and it works great! Thanks A bunch! Luke "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Oh! I think I am idiotized. I'll look over the worksheet when I get home and
let you know what is wrong on my side. Thanks "Bob Phillips" wrote: Luke, I don't know what you have done it is not what I meant at all. You seem to be looking through all 3 columns individually, whereas I set a range to columns C:E, D gets included automatically. And if you notice, the first MAX statement subtracts 1, the second adds 1, this is so that we encompass the 3 rows, above, including, and below the X. Similarly, by INDEX into C and E we encompass columns C, D and E. My original as posted does work. Please try it again, you don't need the change that you made. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Okaay Bob! I got it. You forgot to include colmn B. I think perhaps you knew I would get it:). Here it is In B2 Define Range name "lastX" Refer to as follows: =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",RO W($F$1:$F1)))+1) Then in B2 paste: =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0) )),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(las tX,E2,0)))),(C2&D2&E2)+0,"")) and it works great! Thanks A bunch! Luke "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
I've provided you with an alternative expression using EXACT in your thread:
Array help Part 2, including an explanation of why using FIND wasn't conclusive earlier. Pl follow up in that thread. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Okay then, Per your specs, I re-applied the "Range Name" & "Refers to" and
then entered the second formula into B2. I pulled so hard on the elastic that it came up over my head, now I can see the tag. Not sure what the heck I did but I guess that I entered in the formula wrong earlier. When I changed what you had given me, It did do something similar to the expected results but I am not so sure it would have done me any good down the road. It was easier to start over clean. As always I am amazed at your intelligence with this stuff. Thank you so much Luke "Bob Phillips" wrote: Luke, I don't know what you have done it is not what I meant at all. You seem to be looking through all 3 columns individually, whereas I set a range to columns C:E, D gets included automatically. And if you notice, the first MAX statement subtracts 1, the second adds 1, this is so that we encompass the 3 rows, above, including, and below the X. Similarly, by INDEX into C and E we encompass columns C, D and E. My original as posted does work. Please try it again, you don't need the change that you made. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Okaay Bob! I got it. You forgot to include colmn B. I think perhaps you knew I would get it:). Here it is In B2 Define Range name "lastX" Refer to as follows: =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",RO W($F$1:$F1)))+1) Then in B2 paste: =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0) )),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(las tX,E2,0)))),(C2&D2&E2)+0,"")) and it works great! Thanks A bunch! Luke "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Help Tweak
Luke,
Thanks for re-doing. I was keen to discover if there was some circumstance I had overlooked, it now seems that it works okay for you as given. Regards Bob "Luke" wrote in message ... Okay then, Per your specs, I re-applied the "Range Name" & "Refers to" and then entered the second formula into B2. I pulled so hard on the elastic that it came up over my head, now I can see the tag. Not sure what the heck I did but I guess that I entered in the formula wrong earlier. When I changed what you had given me, It did do something similar to the expected results but I am not so sure it would have done me any good down the road. It was easier to start over clean. As always I am amazed at your intelligence with this stuff. Thank you so much Luke "Bob Phillips" wrote: Luke, I don't know what you have done it is not what I meant at all. You seem to be looking through all 3 columns individually, whereas I set a range to columns C:E, D gets included automatically. And if you notice, the first MAX statement subtracts 1, the second adds 1, this is so that we encompass the 3 rows, above, including, and below the X. Similarly, by INDEX into C and E we encompass columns C, D and E. My original as posted does work. Please try it again, you don't need the change that you made. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Okaay Bob! I got it. You forgot to include colmn B. I think perhaps you knew I would get it:). Here it is In B2 Define Range name "lastX" Refer to as follows: =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($D$1:$D$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))-1):INDEX($E$1:$E$20,MAX(IF($F$1:$F1="X",RO W($F$1:$F1)))+1) Then in B2 paste: =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND(SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0) )),SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))),SUMPRODUCT(--ISNUMBER(MATCH(l as tX,E2,0)))),(C2&D2&E2)+0,"")) and it works great! Thanks A bunch! Luke "Bob Phillips" wrote: Okay, It is a two-step approach. First select cell B2, and add a defined name (InsertNamedefine...) with a name of lastX and a RefersTo value of =INDEX($C$1:$C$20,MAX(IF($F$1:$F1="X",ROW($F$1:$F1 )))-1):INDEX($E$1:$E$20,MA X(IF($F$1:$F1="X",ROW($F$1:$F1)))+1) Then in cell B2, note, leave B1 clear, add the formula =IF(COUNTIF($F1:$F3,"X")0,"",IF(AND( SUMPRODUCT(--ISNUMBER(MATCH(lastX,C2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,D2,0))), SUMPRODUCT(--ISNUMBER(MATCH(lastX,E2,0)))),(C2&D2&E2)+0,"")) and copy down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Luke" wrote in message ... Max asked me to re-post this in order to get more visibility. Originally I asked: Thank you for being there to help us, Is it possible to have a formula in colmn B that will look at any three individual cells in the array C1:E3 and match them up to any one row from the array F1:H11, then display a cancatenation of three like cells from array F1:H11 in Colmn B? I hope this example comes through well enough for you. Thank you, Luke A B C D E F G H 1 359 1 6 3 3 5 9 2 0 0 1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 168 1 6 8 7 1 6 4 8 3 3 4 9 4 4 4 10 950 9 5 0 11 8 8 7 Max Said: =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER( MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G 1&H1)+0,"") <<<<<<<<<<<<<< Worked great for what it was intended to do. Now I am asking if that formula can be tweaked a bit more. for example the formula currently looks at $C$1:$E$3 and matches the corresponding row results in F1:H11 while staying locked into $C$1:$E$3. Now that I see it can be done, I changed my data so that it is all in the same colmns (see example below). Until now I would manually pull out the range $C$1:$E$3 and then I looked for the results.. you helped me with that. In colmn "F" I marked the middle row (C2:E2) of the range $C$1:$E$3 with an "X" and note the "X" at (C10:E10) of $C$9:$E$11 as well as (C17:E17) of $C$16:$E$18 . I would like to know if it is possible to have the same formula keep it's original search until it finds another "X" in colmn "F" then unlock from $C$1:$E$3 (ie $C1:$E3) then lock onto that new range, in this case it's $C$9:$E$11, until it finds another "X" and so on. To be clear, the range that "X" represents would always have three total rows as in the original thread; including the row that contains the "X", one row above and one row below. As well it would not look back at previous ranges but once locked into arange it would keep matching on the current "X" Range until it finds another... I think it's a streatch but I am always amazed at what you guys can do. Thanks again for your help, Luke A B C D E F 1 1 6 3 2 0 0 1 X 3 9 8 5 4 359 3 5 9 5 7 4 4 6 100 1 0 0 7 2 1 3 8 6 4 3 9 168 1 6 8 10 1 6 4 11 3 3 4 12 4 4 4 X 13 9 5 0 14 8 8 7 15 435 4 3 5 16 5 6 1 17 3 0 5 X 18 1 6 8 19 315 3 1 5 20 4 4 5 Thank you for your help. It is alright if it is impossible as I can work with what I have already. Luke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDEX function to return array row. | Excel Worksheet Functions | |||
SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
Display an array of references | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |