Searching Text Strings in Cells

[imported_unknown]

Hi,

I have a problem and I wondered if someon could help.

Basically, My task is to find matching customers from 2 lists. Sounds easy.....the issue is that I have to match by Customer name and the customer names in the list can vary (Ie XYZ Incorporated v XYZ Inc.) So Ive been trying to write a routine iin VBA to do this that can compare the strings for the occurance of 6 matching letters). Ive also0 used the Match function with an index but this only retrieves 250 matches out of 500!!!

Has anybody got any smarter ways of doing this as I dont really want to have to chek each one individually.

Thanks

Mark

 

[plettieri]

Hello markmstevens

Welcome to the board.

It sounds you have no or limited consistency in the lists. Sounds like a fuzzy logic issue but maybe one of the experts here have a better answer.. you might want to post up of a few names in an example to let us see how much different the names are and the formular/code that you have so far.....Is there any pattern where a piece of the names are consistent?

You can use the Colo utility below the message box...


pll
This message was edited by plettieri on 2002-10-06 19:09

 

[PaddyD]

you can do things like:

=vlookup("*"&a1&"*",lookuprange,column,0)

paddy

 

[Yogi Anand]

Hi Mark:

Welcome to the Board!

You do have some viable approaches. It will depend among other things on how large is your data, the nature and type of variation in the names, and so forth.

My worksheet simulation using Advanced Filter is intended to furnish some additional food for thought. In the end, it may turn out that a VBA solution may be the right one for this.

y021006.xls
	A	B	C	D	E	F
1			List1		UsingAdvancedFilter
2			*XYZInc*
3			*XYZLtd*
4			*XYZCor*			sourcedata
5			*XYZPLL*			criteria
6			*XYZCom*			outputfield
7			*XYZBro*
8			*XYZAss*
9			*XYZSoc*
10
11	List1		List1
12	TheXYZCompany		TheXYZCompany
13	XYZAssociates		XYZAssociates
14	XYZAssociation		XYZAssociation
15	XYZBrothers		XYZBrothers
16	XYZCompany		XYZCompany
17	XYZCorporation		XYZCorporation
18	XYZIncorporated		XYZIncorporated
19	XYZLimited		XYZPLLC
20	XYZPLLC		XYZSociety
21	XYZPartners
22	XYZSociety
Sheet5

</SPAN>

Regards!

Yogi

 

[imported_unknown]

Thanks all for helping out and sharing your knowledge. I actually have a further problem that (for example) 1 company is called HP and another is called Hewlett Packard etc. So it may require some manual intervention.

Thanks again and I look forward to discussing problems with you all again.

Mark

 

[stevebausch]

One thing that comes to mind is a sequence:

Sort the list
Select an area of the column that contains variations of the same identity.
Click a button
Button pops up an input box prompting for the correct identity.(Listbox would be interesting, also)
You enter the identity, click OK.
All selected cells get the new identity.


Promise yourself to never let this happen, again.