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?

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.

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.

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.

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.

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!