=EVALUATE("{ to work in a range of cells

F

Fin Fang Foom

I would like to know if its possible without using any addins or vba.
I have a strings of text in a range. Example:

Range

D2 c,b,z
D3 c,b,c
D4 w,h,c
D5 w,q,a
D6 z,p,c
D7 w,q,c

and so on...

So right now I'm using the EVALUATE Function. in the Define Name

Arrays:

=EVALUATE("{"""&SUBSTITUTE(Sheet4!$D$2&","&Sheet4!$D$3&","&Sheet4!$D
$4&","&Sheet4!$D$5&","&Sheet4!$D$6&","&Sheet4!$D$7&","&Sheet4!$D
$8,",",""",""")&"""}")

and using this formula to exact the string of uniques from those
cells.

=INDEX(Arrays,MATCH(TRUE,ISNA(MATCH(Arrays,A$1:A1,0)),0))

The results are:

A2 c
A3 b
A4 z
A5 w
A6 h
A7 q
A8 a
A9 p

The problem I'm having is when I tried to modifiy the EVALUATE
Function into a range it wont work.


=EVALUATE("{"""&SUBSTITUTE(Sheet4!$D$2:$D$9,",",""",""")&"""}")


Is there a way to get the EVALUATE Function to work in a range of
cells?
 
H

Harlan Grove

Fin Fang Foom said:
Arrays:

=EVALUATE("{"""&SUBSTITUTE(Sheet4!$D$2&","&Sheet4!$D$3&","&
Sheet4!$D$4&","&Sheet4!$D$5&","&Sheet4!$D$6&","&Sheet4!$D$7&","&
Sheet4!$D$8,",",""",""")&"""}") ....
The problem I'm having is when I tried to modifiy the EVALUATE
Function into a range it wont work.

=EVALUATE("{"""&SUBSTITUTE(Sheet4!$D$2:$D$9,",",""",""")&"""}")
....

In the first expression, the term

Sheet4!$D$2&","&Sheet4!$D$3&","&Sheet4!$D$4&","&Sheet4!$D$5&","&
Sheet4!$D$6&","&Sheet4!$D$7&","&Sheet4!$D$8

evaluates to a single string, and the full argument to EVALUATE is a
single string. In the second expression, the term

Sheet4!$D$2:$D$9

evaluates to an array, and the full argument to EVALUATE is an array
of strings. The XLM EVALUATE function can't handle array arguments.

So, no, not possible.
 
F

Fin Fang Foom

In the first expression, the term
Sheet4!$D$2&","&Sheet4!$D$3&","&Sheet4!$D$4&","&Sheet4!$D$5&","&
Sheet4!$D$6&","&Sheet4!$D$7&","&Sheet4!$D$8

evaluates to a single string, and the full argument to EVALUATE is a
single string. In the second expression, the term

Sheet4!$D$2:$D$9

evaluates to an array, and the full argument to EVALUATE is an array
of strings. The XLM EVALUATE function can't handle array arguments.

So, no, not possible.

Thank You Harlan Grove,


Too bad there is not away around this using native functions in excel.
 
F

Fin Fang Foom

Thank You Harlan Grove,

Too bad there is not away around this using native functions in excel.


Harlan,

I have cells from D2:D300 how can I get the EVALUATE to work with that
many cells? I tried to add more cell locations in the Define Name box
but it has it limits, I think but not sure it has to do with the 255
characters.
 
H

Harlan Grove

Fin Fang Foom said:
I have cells from D2:D300 how can I get the EVALUATE to work with that
many cells? I tried to add more cell locations in the Define Name box
but it has it limits, I think but not sure it has to do with the 255
characters.

Divide & conquer.

Temp1 defined as
=WS!$D$2&","&WS!$D$3&","&WS!$D$4&","&WS!$D$5&","&WS!$D$6&","&
WS!$D$7&","&WS!$D$8&","&WS!$D$9&","&WS!$D$10&","&WS!$D$11&","&
WS!$D$12&","&WS!$D$13&","&WS!$D$14&","&WS!$D$15&","&WS!$D$16

Temp2 defined as
=WS!$D$17&","&WS!$D$18&","&WS!$D$19&","&WS!$D$20&","&WS!$D$21
&","&WS!$D$22&","&WS!$D$23&","&WS!$D$24&","&WS!$D$25&","&WS!$D$26
&","&WS!$D$27&","&WS!$D$28&","&WS!$D$29&","&WS!$D$30&","&WS!$D$31

etc, then

Temp defined as
=Temp1&","&Temp2&","& . . . &","&Temp20

Then define Arrays as

=EVALUATE("{"""&SUBSTITUTE(Temp,",",""",""")&"""}")

Excel provides no generalized concatenation function, so concatenating
large sets of cells requires doing it one by @#$% one. If you want to
avoid VBA, there is no alternative.
 
F

Fin Fang Foom

Divide & conquer.

Temp1 defined as
=WS!$D$2&","&WS!$D$3&","&WS!$D$4&","&WS!$D$5&","&WS!$D$6&","&
WS!$D$7&","&WS!$D$8&","&WS!$D$9&","&WS!$D$10&","&WS!$D$11&","&
WS!$D$12&","&WS!$D$13&","&WS!$D$14&","&WS!$D$15&","&WS!$D$16

Temp2 defined as
=WS!$D$17&","&WS!$D$18&","&WS!$D$19&","&WS!$D$20&","&WS!$D$21
&","&WS!$D$22&","&WS!$D$23&","&WS!$D$24&","&WS!$D$25&","&WS!$D$26
&","&WS!$D$27&","&WS!$D$28&","&WS!$D$29&","&WS!$D$30&","&WS!$D$31

etc, then

Temp defined as
=Temp1&","&Temp2&","& . . . &","&Temp20

Then define Arrays as

=EVALUATE("{"""&SUBSTITUTE(Temp,",",""",""")&"""}")

Excel provides no generalized concatenation function, so concatenating
large sets of cells requires doing it one by @#$% one. If you want to
avoid VBA, there is no alternative.


Thank You Harlan its perfect!
 
F

Fin Fang Foom

Thank You Harlan its perfect!- Hide quoted text -

- Show quoted text -

Harlan,


When I added more cells in the Define Name area it gives me a VALUE
error. Here what I got:

Arrays =EVALUATE("{"""&SUBSTITUTE(Temp,",",""",""")&"""}")

Temp =Temp1&","&Temp2&","&Temp3

Temp1 ='Unique Strings'!$D$2&","&'Unique Strings'!$D$3&","&'Unique
Strings'!$D$4&","&'Unique Strings'!$D$5&","&'Unique Strings'!$D
$6&","&'Unique Strings'!$D$7&","&'Unique Strings'!$D$8

Temp2 ='Unique Strings'!$D$9&","&'Unique Strings'!$D$10&","&'Unique
Strings'!$D$11&","&'Unique Strings'!$D$12&","&'Unique Strings'!$D
$13&","&'Unique Strings'!$D$14&","&'Unique Strings'!$D$15

Temp3 ='Unique Strings'!$D$16&","&'Unique Strings'!$D$17&","&'Unique
Strings'!$D$18&","&'Unique Strings'!$D$19&","&'Unique Strings'!$D
$20&","&'Unique Strings'!$D$21&","&'Unique Strings'!$D$22

It works for Temp1 & Temp2 but if I add Temp3 it gives me that error.
What do you think the problem is?
 
H

Harlan Grove

Fin Fang Foom said:
When I added more cells in the Define Name area it gives me a VALUE
error. Here what I got:

Arrays =EVALUATE("{"""&SUBSTITUTE(Temp,",",""",""")&"""}")

Temp =Temp1&","&Temp2&","&Temp3 ....
It works for Temp1 & Temp2 but if I add Temp3 it gives me that error.
What do you think the problem is?

I should have foreseen this. EVALUATE's argument can only be so long,
and it appears you've bumped into that limitation. At this point I
believe you need to abandon any hope of elegance and use a LOT of cell
formulas. Basically, you'd need to split out the contents of
<whatever>!D2:D300 into separate cells.
 
F

Fin Fang Foom

I should have foreseen this. EVALUATE's argument can only be so long,
and it appears you've bumped into that limitation. At this point I
believe you need to abandon any hope of elegance and use a LOT of cell
formulas. Basically, you'd need to split out the contents of
<whatever>!D2:D300 into separate cells.

Thank you Harlan I will split them into into multiple columns. To try
to get the uniques of it.

Once again Thank You!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top