I did a search for comparing column and found one of my old postings.
http://www.microsoft.com/office/comm...xp=&sloc=en-us
Your problem has been addressed a number of times. There really isn't any
good algorithm for best matching two sets of strings. It a problem
scientists have addressed for a very long time.
To minimize the problem, the best thing to do is to try to keep common names
the same in both lists a periodically check to make sure there aren't the
same name in both lists but speeled differently.
Now what should you do? I don't know the answer. Some people like to come
up with a list of the just the people who aren't in both lists. Then
highlight these names in both lists. Others like to put the lists
alphabetically side by side leavig gap when the two lists don't match.
I don't know how many differences you have between the two lists and how big
each list is to determine the best method. I also don't know your
requirements. Do you want to merge the lists or keep them seperate? Do you
want to sort the list by names or some other criteria.
I also don't know what your plan is when you find names that a spelled
differrently in each list.
I have worked with list for almost thirty years where this problem keeps on
reoccuring. I work more with number where people have entered wrong numbers
and had to find these bad entries. It is painful to keep multiple databases
consistant when humans are entering tthe data incorrectly.
Come up with a plan, and I will assit as necessary. Learning how to use the
find command is the right thing to do. You can check each list against the
other and highlight the ones that don't match or do match. But you still wil
have to manually check the list. With 4000 entries, this takes time.
"Joel" wrote:
> I would fix the database. The simpliest way of finding the entries that
> don't match is to put both lists in the same column and sort the list. Then
> remove duplicate entries which will be on adjacent rows. Hopfully there will
> be only a few mis-matches.
>
> "(E-Mail Removed)" wrote:
>
> > Hi,
> >
> > Being a relatively new commer to VBA, I am wondering if there is a
> > good way to go about comparing columns of text in Excel. Basically I
> > have a list of company names from one database and I am comparing it
> > to a list of company names from another database. My problem is that
> > the entry in one database will be slightly different from another.
> > For an example one of the lists might have: Frank's Welding Company,
> > but the other will have something like: Frank's Welding Company, LLC.
> > As a result the Vlookup command will not return very many results. Is
> > there an easier way to go about this than say checking letter by
> > letter while ignoring specifics like llc, the and others?
> >
> > Furthermore to compound my problem, the list I am comparing it against
> > is some 4,000 entries long which means it will take forever to run
> > through the list entry by entry. I have played around a little bit
> > with Find, but I am not fimiliar enough with the command to use it
> > properly. In otherwords can I tell it to Find "Fr" at the start of an
> > entry and therfore skip to that section in the list?
> >
> > Any help will be greatly appreciated.
> >
> > Justin
> >
> >
Similar Threads
