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

[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?

 

[Harlan Grove]

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.

 

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

 

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

 

[Harlan Grove]

I have cells from D2300 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.

 

[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!

 

[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?

 

[Harlan Grove]

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>!D2300 into separate cells.

 

[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>!D2300 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!