Expression Equivalent to this Excel Program?
I have a huge workbook containing some dozen or so sheets of several hundred thousand rows each. The file is so large that I really need to convert it to access in order to make it useable. I now need to know if an expression will accomplish the same task as the formula I've been using in Excel. I have no experience with Access.
[From my original excel forum post] In essence, my file is a list of names, first and last. What I'd like to do is search through the list (in particular, the LName column) and, if I find a match to any one of a couple dozen different names (including alternate spellings), the program will write a word (the standardized spelling) to the adjacent cell. I've attached a sample file so you can better understand what it is I'd like to do. The sample workbook includes the dictionary of names/alternative spellings that I'd like to use, on a second sheet.
I originally used this formula, courtesy of JBeaucaire:
"=IF(ISNUMBER(MATCH(C2,'Alt Spellings'!$B:$B,0)),INDEX('Alt Spellings'!$A:$A,MATCH(C2,'Alt Spellings'!$B:$B,0)),IF(ISNUMBER(MATCH(C2,'Alt Spellings'!$C:$C,0)),INDEX('Alt Spellings'!$A:$A,MATCH(C2,'Alt Spellings'!$C:$C,0)), C2))"
So, can I do this in Access? Any tips for making the database? And writing the expression? As I said, I'm totally new to Access. Thank you all for your help!
What you are asking for can be done in Access, however if you are totally new to Access you need to learn to use it well before attempting such an endevor. Learn about relational databases, unique indexes and keys, and SQL. Try putting your sample into a database with two tables, create a form to perform whatever search you are trying to do. Once you've gotten as far with that as you can, post the database and ask help on specific issues that you are having.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Thank you for your response!
While I am certainly willing to put in the time and effort to learn what you state above, if basic statistical analysis (including frequency analysis) is what I'm interested in, does Access sound like the program to use? Excel would have been appropriate if there weren't so very many entries.
I'd think Excel would work fine. You're need isn't really complex enough to warrant setting up a db in Access.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks