Searching Text Strings in Cells

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you can do things like:

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

paddy
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEF
1List1UsingAdvancedFilter
2*XYZInc*
3*XYZLtd*
4*XYZCor*sourcedata
5*XYZPLL*criteria
6*XYZCom*outputfield
7*XYZBro*
8*XYZAss*
9*XYZSoc*
10
11List1List1
12TheXYZCompanyTheXYZCompany
13XYZAssociatesXYZAssociates
14XYZAssociationXYZAssociation
15XYZBrothersXYZBrothers
16XYZCompanyXYZCompany
17XYZCorporationXYZCorporation
18XYZIncorporatedXYZIncorporated
19XYZLimitedXYZPLLC
20XYZPLLCXYZSociety
21XYZPartners
22XYZSociety
Sheet5
</SPAN>

Regards!

Yogi
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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

Well-known Member
Joined
May 11, 2002
Messages
810
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.
 

Forum statistics

Threads
1,144,148
Messages
5,722,791
Members
422,458
Latest member
Muirzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top