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

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 a

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?

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

Fin Fang Foom wrote...
....
Arrays:

=EVALUATE("{"""&SUBSTITUTE(Sheet4!$D$2&","&Sheet4 !$D$3&","&
Sheet4!$D$4&","&Sheet4!$D$5&","&Sheet4!$D$6&","&S heet4!$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&","&Sh eet4!$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.

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

In the first expression, the term

Sheet4!$D$2&","&Sheet4!$D$3&","&Sheet4!$D$4&","&Sh eet4!$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.

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

On May 8, 5:02 pm, Fin Fang Foom wrote:
In the first expression, the term

Sheet4!$D$2&","&Sheet4!$D$3&","&Sheet4!$D$4&","&Sh eet4!$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.


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.

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

Fin Fang Foom wrote...
....
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&","&W S!$D$6&","&
WS!$D$7&","&WS!$D$8&","&WS!$D$9&","&WS!$D$10&","&W S!$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.

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

On May 8, 8:57 pm, Harlan Grove wrote:
Fin Fang Foom wrote...
...

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&","&W S!$D$6&","&
WS!$D$7&","&WS!$D$8&","&WS!$D$9&","&WS!$D$10&","&W S!$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!

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

On May 9, 1:17 pm, Fin Fang Foom wrote:
On May 8, 8:57 pm, Harlan Grove wrote:





Fin Fang Foom wrote...
...

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&","&W S!$D$6&","&
WS!$D$7&","&WS!$D$8&","&WS!$D$9&","&WS!$D$10&","&W S!$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!- 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?

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

Fin Fang Foom wrote...
....
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.

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

On May 9, 3:08 pm, Harlan Grove wrote:
wrote...
...

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.

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

Once again Thank You!