Aug 03 2019 12:36 PM
I have two columns (A and B). Column A contains the dates and Column B contains the names of people who are required to attend the duty. Now based on this can i have names of people and just below their names, I want the all the dates that they are required to report to duty.
Aug 09 2019 02:09 AM
Hi Nabil,
There is no such exact action in user interface. You may select column, from right click menu select Transform with any function, e.g. Trim, and after that in formula bar change Text.Trim on any other suitable function, in our case Splitter.SplitTextByDelimiter(",")
Aug 09 2019 03:59 AM
Out of curiosity, why
= (COLUMN(N1:Z1)-COLUMN($N$1))
rather than
= SEQUENCE( 1, 13, 0 )
Do these columns have any particular significance or is it a case of old habits dying hard?
p.s. You seem to be able to work magic with this user interface where I struggle to produce anything but basic text!
Aug 09 2019 06:40 AM
Hi Peter,
You are right, SEQUENCE is much better. I took basic pattern with regular formula and just wrapped it by UNIQUE, didn't think about farther improvement. Constant switching between DA and pre-DA Excel doesn't help...
Which interface do you mean, this editor?
Aug 09 2019 07:38 AM
It was your indented code with the pretty colours that I noticed.
I also have problems with warnings of invalid HTML, especially following cut-and -paste. Submit the post a second time and it all comes good.
"Constant switching between DA and pre-DA Excel doesn't help..."
That will only get worse as one's ways of thinking change to build on DAs. I tend to think in terms of creating entire arrays and it will come as a shock working for clients with old versions of Excel (including 2019)!
Aug 09 2019 10:12 AM
Intended code that's manual work with Notepad++ which I usually use for long formulas; or Alt+Enter in formula bar. There are some tools for formulas formatting but I still prefer manual one.
Aug 09 2019 10:54 AM
Hi
What does the "Evaluate" function do?
would it still work if we have lots of names (> 255 Character)
Thanks
Aug 09 2019 11:08 AM
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.
Aug 09 2019 11:16 AM
Forgot to say, EVALUATE is not recalculated. The workaround is to wrap it with some volatile function.
Aug 09 2019 12:43 PM
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.
Aug 18 2019 01:30 AM
@Twifoo cant thank you enough for this.I was unwell for a while so could not thank you earlier. There were wonderful suggestions but for me this solution works best. You have saved so much of my time. You people are simply awesome.
Aug 18 2019 02:07 AM