extract unique values matching a text string

Copper Contributor

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. Screen Shot 2019-08-04 at 1.05.39 AM.png

30 Replies

@nabilmourad 

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(",")

 

@Sergei Baklan 

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!

@Peter Bartholomew 

 

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?

@Sergei Baklan 

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)!

@Peter Bartholomew 

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.

@Peter Bartholomew 

Hi

What does the "Evaluate" function do?

would it still work if we have lots of names (> 255 Character)

Thanks

@nabilmourad 

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.

 

@nabilmourad 

Forgot to say, EVALUATE is not recalculated. The workaround is to wrap it with some volatile function.

@nabilmourad 

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.

@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.

Your gratitude delights me and I wish you all the best!