Forum Discussion
extract unique values matching a text string
I think I have just about achieved the other part of deriving a list of unique names!
I started by defining a named formula 'string' that runs all of the individual lists of names together
= "{""" & SUBSTITUTE( TEXTJOIN( ", ", 1, PersonsRequired ), ",", """,""" ) & """}"
and then wraps them in double quotes and braces to create a string version of an array constant.
A further named formula 'split' uses the old Macro4 function
=EVALUATE(string)
to create an array of individual person names.
I am not quite home and dry because one of the 'Sam's has 4 characters whereas the other two have the correct value. This messes up the identification of unique values
= UNIQUE( split, TRUE )
Hi
What does the "Evaluate" function do?
would it still work if we have lots of names (> 255 Character)
Thanks
EVALUATE
This is a 'left over' from the old Macro language that preceded VBA. In modern Excel it is only recognised if it is used within a Named Formula. It is used to evaluate a formula or expression that is in the form of text and return the result.
Syntax
= EVALUATE(formula_text)
Formula_text is the expression in the form of text that you want to evaluate.
Remarks:
Using EVALUATE is similar to selecting an expression within a formula in the formula bar and pressing the Recalculate key (F9 in Microsoft Excel for Windows). EVALUATE replaces an expression with a value.
Note: A named formula is just a snippet of a formula held as a text string. It is only processed when a formula placed within a cell refers to it, in which case the snippet is calculated as if it were an inner element of the cell formula, nested within it. I used named formulas to give meaning to the values calculated by the inner elements of a nested formula and to make the cell formula shorter and more easily understood. Other plusses are that a named formula is always evaluated as an array formula and, in this case, it is Excel's most comprehensive calculation environment.
Forgot to say, EVALUATE is not recalculated. The workaround is to wrap it with some volatile function.
EVALUATE evaluates formula defined as text, it works if you add entire expression to name manager, e.g
=EVALUATE("A1+B1") named as MyFormula and called as =MyFormula. You shall save your workbook in macro-enabled format.
Have no idea how long the text could be.
