Correcting a field with misspelled entries

bigmeadow24

Board Regular
Joined
Jan 13, 2011
Messages
51
I just started using Access 2010 recently and I'm a bit lost - looking for a point in the right direction.

I've been using Excel 2010 to manage some extremely large data sets, and I've been running several vlookups on the data and my files are starting to get very bogged down. One vlookup is designed to "fix" a misspelled company entry. I have a column that has a list of many many companies, but the problem is that the underlying data is messy - one entry might say "Microsoft", one might say "Microsoft Company", while a third might have been entered by a drunk person and says "Mcrofosti". I have a separate Excel file that has a list of every misspelling I've come across (over 5000 to date), and I use a vlookup to force all misspelled entries to one standard company name.

I'd like to move this step (and several others like it) to Access, since my understanding is Access can handle large data sets more smoothly than Excel. I can then export a nice clean data set into Excel and go from there. I just don't know how exactly to set up my tables and link them together to recreate the above process in Access. I don't necessarily need the solution spelled out for me, just a gentle shove towards the right starting point or something. Thank you!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

HiTechCoach

Well-known Member
Joined
Aug 29, 2010
Messages
1,008
From what you have describe screams that you really need a relational database, like Access . Why? Because in a well design (normalized) database you would only have the name "Microsoft" entered once. That is the key to data integrity.

Excel is a great tool. I use it often for analyzing and reporting. Never for storing and maintaining data. That is not what it is designed to do.

As long as you keep trying to maintain the data in Excel the issue will not go away.
 

bigmeadow24

Board Regular
Joined
Jan 13, 2011
Messages
51
Yeah, I've realized/accepted that fact and am trying my best to dive in, but the only things I've done in Access are tutorials using really obvious relational data sets like addresses and stuff. The data set I'm using isn't really like that, so trying to shoehorn my limited Access skills into something a bit beyond me has been more difficult than I had thought/hoped.
 

HiTechCoach

Well-known Member
Joined
Aug 29, 2010
Messages
1,008
Making the transition from spreadsheet design to relational database design is a huge leap in methodology.

If all you want to do is clean up the data in an Excel spreadsheet that is just rows of data then you probably can use Access to help clean up the data. You could import the data from excel into a single table. Run update queries to clean up the data. Export the data back to excel. Lather, rinse, repeat on a regular basis to keep the data cleaned up.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,489
Members
430,358
Latest member
zzc1128

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