Comparing and replacing data

[accessdummy]

Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.

Ｔｈｅｒｅｆｏｒｅ　Ｉ need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.

Is that possible using Access? if not, how about excel? please suggest.

thanks.

 

[gemma-the-husky]

very difficult to be precise - how many names do you have in each column?

if you only have a relative few, put all the names in one column, with a source indicator, and run down it in sorted order.

-------------------------------------
if you have a lot, then you need a "play" dbs while you are manipulating the data to do this.

copy the names tables inot the play dbs.

Then in the play dbs create a new table for the merged data, Have three columns, for "UseName", "NamefromTableA" and "NamefromTableB" - move any exact matches into that table, You are now only bothered about unmatched data

Access ignores capitalization which helps

you probably need to create a new column in each table called massagedname. then populate this column by massaging your names - i would suggest stripping out all characters other than letters, and then perhaps comparing the first few characters only between the two tables

do a normal join, and find all the hits

then do a find unmatched from each table to the other to find floating values.

you might pick up a few cross matches this way, but it might help.

------------------------------------------------

as you find massaged matches, you can move these into your new table, until hopefully you end up with the merged table containing all the data

unless you know you have the same numbers of names in each table, you may find you have some namesi TableB only, and some in TableB

----------------------------------------
you should gradually get there - the main problem will be if the names start differently. Having ltd or limited at the end is no great issue.

 

[accessdummy]

this sound really complicated. do you have an example?? or maybe i attach a db for u to help me out eh?

 

[gemma-the-husky]

how many names do you have in each table?

 

[accessdummy]

how many names do you have in each table?

Hi Gemma, i have attached the spreadsheet.

I have 2 columns with unequal amount of data. Col A has about 500 names and Col B has about 800 names or so.

please help. cheers