PDA

View Full Version : Sleeper: Look up & return



Tony Singh
01-08-2014, 05:15 AM
Hi

In the attached file, I require a formulae to search the items in Column E in Column A and return the match in Cell B.

Any help would be appreciated.

Thanks
Tony

Bob Phillips
01-08-2014, 09:31 AM
I am looking in column B, and I cannot see where you get those values from. Most I look at don't match.

SamT
01-08-2014, 10:25 AM
Many like this

(A) (B) (E)
DANIEL WATNEY - MAURICE HATTER MOORGATE <> MAURICE <> DANIEL WATNEY-MAURICE HATTER

Tony Singh
01-09-2014, 09:31 AM
xld

I need to find the closest match. The background is that, one section is from an accounts package that contain account codes & the other list comes from an Excel file used to send periodic work to subcontractors. The idea is to allocate the Acc code to the periodic Excel file so that I can import this into a programme that contains the account codes so they can be allocated.

Hope this makes sense.

Tony Singh
01-09-2014, 09:34 AM
Sam

Column A, which are names of the Client, comes from an accounts package along with column B which are account codes, and the 3rd Column or <> as you indicate, is from an excel file that only contains the names of the Clients. I need to find the closest match for as many obtain the reults then manually complete the rest.

Hope this makes sense.

SamT
01-09-2014, 01:04 PM
For each value in Column E, Find most similar value in column A, Return Value from Column B.

It is the "Most Similar" that is stopping me.

It will require Regex expressions to solve this and I just don't know them.

"DANIEL WATNEY - MAURICE HATTER MOORGATE" is not an exact match for "DANIEL WATNEY-MAURICE HATTER"

Recommended Solution:

In the attached, I have started aligning the Clients, Contract Names, and Sage/C-Links to create a Cross reference table. If you will complete the table it will be very easy to provide what you need. Either with a very simple VLookUp formula or a simple VBA Function as the usage of the Sage/C-Link indicates.

Be sure to include every spelling of the Client Values even if they apply to the same Contract. Even though the table will only be used for the Clients and Sage/C-link values, I would leave the Contract Names in for possible future use.

Tony Singh
01-10-2014, 06:03 AM
SamT thanks for that, nut I am having problems opening the file. I have Office 2010. I will see the file then start the work as you say. See if you can re send again.

Thanks, Tony

SamT
01-10-2014, 09:28 AM
*.xlsm format