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...

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...

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...

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...

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...

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...

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...

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...

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
news
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

welcome back, max, at least the 999 has re-acted responsively bg,
For this post, you almost got it - Case sensitive - I paste the formula and
it grab X1Y1Z1, but it is static - when I try to test run the formula by
changing data as follows....
from C2 : X1 into X2
from D2 : y1 into y2
from E2 : Z1 into Z2
from F6 : X into X1
from G6 : y into y1
from H6 : Z into Z1
then on B6 : the result is X1y1Z1 ? the individual cellS on C1:E3 do not
contain these since I change it already with suffix no 2. Is it static or
some formula modification is needed...Pls. take note that the data on C1:E3
should contain varying numbers of character (like cell refs A1-IV65536)
thanks and pls. dont hang up...


"Max" wrote:

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
news
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