On Wed, 15 May 2013 06:34:03 +0100, aikhalif wrote:


Thank you for responses Ron Rosenfeld and ... I appreciate it.

I could get the result. However, my analysis requires one more higher
level than previouusly mentioned - Sample provided below.

ColumnA ColumnB ColumnC ColumnD
command City command command
exc City axisid excel
Command Town myer prop command 1
myer plc Suburb sports metrics myer prop
axis pty State excel axisid
pivot Country toblerone pivott table
toble State pivott table toblerone
chair p/l City 6 chair 6 chair
chair loop CIty command 1 6 chair
ex myer prop State myer prop
super sports chair Country sports metrics
500 electrics State -
SM co City Sports metrics
sports chair 6 country 6 chair


I would like this to be as an excel formula, than as a VB coding.

Thanks guys.

With regard to an Excel formula, rather than a User Defined Function, I will leave that for someone else. It could probably be done with multiple helper columns, or long and difficult to maintain formulas.

So far as your additional requirements, the examples you post do not allow me to make unambiguous rules.

For example:

ex myer prop -- myer prop BUT ex also provides a partial match with excel
SM co -- Sports Metrics BUT SM co does NOT match Sports Metrics, and co matches command

It is also not clear to me what rule should be applied for:
command -- command
Command -- command 1

On your data above, the UDF I provided returns the following:
command command
exc excel
Command command
myer plc myer prop
axis pty axisid
pivot pivott table
toble toblerone
chair p/l 6 chair
chair loop 6 chair
ex myer prop excel
super sports chair sports metrics
500 electrics
SM co command
sports chair 6 sports metrics