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!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,522
Messages
5,548,545
Members
410,848
Latest member
anuradhagrewal
Top