cell comparison

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I am suing Excel 2002. I have a large worksheet that has approx 22,000
records [and growing]. One of the columns is "customer name" where the
names have been input inconsistently, for example:
1. John's guitar shoppe
2. Johns Guitar shop
3. Johns Guitar shop, Inc.

Is there a function that would automatically correct these inconsistencies
based on a table and triggered with a Vlookup?

Thanks!

Alan
 
I don't think lawyers will help <bg>.

There is no function like this.

you could sort your range by this column and try to eyeball it. Maybe some
helper columns that have things like:

=IF(D2=D3,"duplicate",IF(LEFT(D2,$D$1)=LEFT(D3,$D$1),
"Close on: " & $D$1 & " Chars","not close"))

(Put a nice number 5, 10, or 15 in D1. You could vary that number to help find
the close, but no match.)

If the existing order of your data is important, use another helper column. Put
1 in row 1, 2 in row 2 and select both those cells and drag down through your
data. You can use that to resort it when you're done.


I am suing Excel 2002. I have a large worksheet that has approx 22,000
records [and growing]. One of the columns is "customer name" where the
names have been input inconsistently, for example:
1. John's guitar shoppe
2. Johns Guitar shop
3. Johns Guitar shop, Inc.

Is there a function that would automatically correct these inconsistencies
based on a table and triggered with a Vlookup?

Thanks!

Alan
Click to expand...
 
Alan,

This is probably not the exact solution you're looking
for, but its better than editing each and every entry or
copy/pasting a thousand times.

Search/replace (Ctrl-H) has a limited ability to use
wildcards. ? to match any one character and * to match any
sequence of characters. You could run a series of
search/replaces in which you standardize your entries.

For example, if you ran the following search/replace on
the sample data you provided, they would all end up
identical.

Replace John*uitar shop*

HTH,
Ryan
 
Sorry, I forgot to type in the "replace" part.

Replace John*uitar shop*
with John's Guitar Shop

-----Original Message-----
Alan,

This is probably not the exact solution you're looking
for, but its better than editing each and every entry or
copy/pasting a thousand times.

Search/replace (Ctrl-H) has a limited ability to use
wildcards. ? to match any one character and * to match any
sequence of characters. You could run a series of
search/replaces in which you standardize your entries.

For example, if you ran the following search/replace on
the sample data you provided, they would all end up
identical.

Replace John*uitar shop*

HTH,
Ryan
-----Original Message-----
I am suing Excel 2002. I have a large worksheet that
Click to expand...
has
approx 22,000
records [and growing]. One of the columns is "customer name" where the
names have been input inconsistently, for example:
1. John's guitar shoppe
2. Johns Guitar shop
3. Johns Guitar shop, Inc.

Is there a function that would automatically correct these inconsistencies
based on a table and triggered with a Vlookup?

Thanks!

Alan


.
Click to expand...
.
Click to expand...
 
Back
Top