| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array help Part 2
Almost got it !
from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
maybe you need to re-post on other forum
"driller" wrote: Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
The probelm is this portion:
(F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Almost got it Biff,,,but when i try to change
C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
the result on Col.B seems unusual...
What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Biff, here is the complete formula
B1=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)))),IF(ISERROR((F1&G 1&H1)+0),F1&G1&H1,(F1&G1&H1)+0),"") then here is the table with result on Col. B A B C D E F G H 1 1 * 0 3 5 9 2 X1 y1 Z1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 XyZ X y Z 7 1 6 4 8 zZ* z Z * 9 4 4 4 10 950 9 5 0 11 XxZ X x Z From Above :B6, B8 and B11, has nothing to do with data from C2:E2 the requested benefit here is the that the data on C2:E2, must accomodate any number of character in each cell, which may be assigned as cell refs. like IV65536 or search for text strings...by modifying the concatenated result. Is it possible ? Thanks "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Since it appears you want a partial, case sensitive search for the reference
range C1:E3, try this modification which uses FIND instead of MATCH In B1, copied down: =IF(AND(SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,G1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,H1)))),IF(ISERROR((F1&G1&H 1)+0),F1&G1&H1,(F1&G1&H1)+0),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "driller" wrote in message ... Biff, here is the complete formula B1=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)))),IF(ISERROR((F1&G 1&H1)+0),F1&G1&H1,(F1&G1&H1)+0),"") then here is the table with result on Col. B A B C D E F G H 1 1 * 0 3 5 9 2 X1 y1 Z1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 XyZ X y Z 7 1 6 4 8 zZ* z Z * 9 4 4 4 10 950 9 5 0 11 XxZ X x Z From Above :B6, B8 and B11, has nothing to do with data from C2:E2 the requested benefit here is the that the data on C2:E2, must accomodate any number of character in each cell, which may be assigned as cell refs. like IV65536 or search for text strings...by modifying the concatenated result. Is it possible ? Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
excuse me Biff, are you still around ? I'll catch back to this thread
tomorrow or either re-post it tomorrow, if you dont mind...I'll go for a break...thanks for trying... "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
I don't know what you're asking about here.
That formula was written for a *specific situation* and by changing the data it no longer fits that *specific situation*. Biff "driller" wrote in message ... excuse me Biff, are you still around ? I'll catch back to this thread tomorrow or either re-post it tomorrow, if you dont mind...I'll go for a break...thanks for trying... "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =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,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| defintion of array function | Excel Discussion (Misc queries) | |||
| Transpose words and numbers into array of different proportions | Excel Discussion (Misc queries) | |||
| Array not working correctly Returns FALSE on second part | Excel Discussion (Misc queries) | |||
| What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
| VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Hybrid Mode
