Correcting a field with misspelled entries

[bigmeadow24]

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!

 

[HiTechCoach]

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]

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]

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.