| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| evaluate by a "mask" | Excel Discussion (Misc queries) | |||
| Possible to "rotate" range of cells so columns are rows and vice versa? | New Users to Excel | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| In excel counting cells in a range which meet condition "Xand<X" | Excel Worksheet Functions | |||
| HOW to Evaluate a range with IF ? | Excel Discussion (Misc queries) | |||
Linear Mode
