ExcelExpert
New Member
- Joined
- Jul 24, 2012
- Messages
- 1
Here's a great problem that even I can't solve, but I see possible glimmerings of a solution.
I have a giant database (250,000 records) and a smaller to-find list (35,000 records), though the sizes are a problem. These are both lists of peoples names and addresses from all around the country. The main problem is to find all the records in the database which are also in the to-find list.
The matching criteria to determine a 'match' between the to-find list and the database requires an exact match between the last name and state, and a psuedo-(alias)-match to the first name.
Its easiest to explain by an example.
Here's what I mean by 'psuedo-(alias)-match": I have a third list, the "alias list", of about 800 rows by 18 columns. Each row has a list of equivalent first names, thus one row might contain Marc, Mark, Marcus, and Marley. (Some names, like Elizabeth, have 17 aliases.)
So in the to-find list there could be an entry where : first name = Marcus , last name = Aurelius and state = NY.
In the database there might be an entry where : first name = Mark, last name = Aurelius, and State = NY, and there might also be an entry first name = Marcantonio, last name = Aurelius, and State = NY.
I want the resulting table of matched entries to contain Mark Aurelius NY but not Marcantinio Aurelius NY , because Mark is in the alias list for Marcus but Marcantonio is not in the alias list for Marcus.
Notice that the first-name alias list, might not have the first name from the to-find list (Marcus), in the first column of the row in the alias list; Marcus, as used in the example, could be in the third column and Mark, the name in the database, is in the first (or any other) column.
I have gotten this to partly work, but not quite all the way. I'm using the Advanced Filter to create an output table of the matching records. The Criteria Range for the Advanced Filter contains the entire list of Last Names and States from the To-Find list, and without considering the fusrt name psuedo (alias) match, works perfectly as you'd expect, deliveing all the records from the database that are exact matches for both the last name and the state.
I have also created a list of Range Names for the Alias List, each Name being one rwo of the Alias List and the name being the first name which is in the first column of the alias list. Thus, using my example, the name Mark (and all the others) are contained in a range named Marc.
For the first name psuedo-(alias)-match, I have added a third column to the Criterion range. This column has a blank column heading, unlike the other two which are (obviously) titled Last and State. In the actual cells in the Criteia range for this third column, is an equation of mthe following general form:
=COUNTIF(Marc,'Database'!C8)
where Marc is the name of the range containing the aliases for the first name that's in the to-find list. C8 is the first cell of the database column on the Database sheet where the first names are.
This actually works! But I only if I TYPE IN the range name that contains the first name in the corresponding row of the to-find list. Since there are 35,000 rows in the Criteria range, its impossible to type in all the range names by hand. Especially since both the database and the to-find lists are ever-changing.
I do have a means of looking up, automatically, with equations, the range name which contains the first name in the to-find list. So right next to this column of the criteria range, but not part of the criteria range, is the equation which does this, and it has the word marc as its result.
If I could figure out some way of transferring the equation result Marc into the COUNTIF function of the equation in the criteria range, I'd have a solution!
I have tried indirect addressing and vlookup to do this. Nothing works except directly typing the name Marc into the COUNTIF formula.
Anybody got any other ideas? Its been a real brain teaser that I have been struggling with for two weeks.
I would perfer NOT to use a macro, the only reason being that I am not very good at writing macros and it would take me forever to figure out this one. I am, however, and expert at equations. There may be an entirely different approach to this (still excluding macros), and if anyone has any clever ideas, I'd waiting to hear them!
Hope you understand the question, its somewhat hard to explain without showing the spreadheet (which is 127 meg in size!).
Thanks for your thoughts, hopes, ideas and prayers.
>>>> Bill
I have a giant database (250,000 records) and a smaller to-find list (35,000 records), though the sizes are a problem. These are both lists of peoples names and addresses from all around the country. The main problem is to find all the records in the database which are also in the to-find list.
The matching criteria to determine a 'match' between the to-find list and the database requires an exact match between the last name and state, and a psuedo-(alias)-match to the first name.
Its easiest to explain by an example.
Here's what I mean by 'psuedo-(alias)-match": I have a third list, the "alias list", of about 800 rows by 18 columns. Each row has a list of equivalent first names, thus one row might contain Marc, Mark, Marcus, and Marley. (Some names, like Elizabeth, have 17 aliases.)
So in the to-find list there could be an entry where : first name = Marcus , last name = Aurelius and state = NY.
In the database there might be an entry where : first name = Mark, last name = Aurelius, and State = NY, and there might also be an entry first name = Marcantonio, last name = Aurelius, and State = NY.
I want the resulting table of matched entries to contain Mark Aurelius NY but not Marcantinio Aurelius NY , because Mark is in the alias list for Marcus but Marcantonio is not in the alias list for Marcus.
Notice that the first-name alias list, might not have the first name from the to-find list (Marcus), in the first column of the row in the alias list; Marcus, as used in the example, could be in the third column and Mark, the name in the database, is in the first (or any other) column.
I have gotten this to partly work, but not quite all the way. I'm using the Advanced Filter to create an output table of the matching records. The Criteria Range for the Advanced Filter contains the entire list of Last Names and States from the To-Find list, and without considering the fusrt name psuedo (alias) match, works perfectly as you'd expect, deliveing all the records from the database that are exact matches for both the last name and the state.
I have also created a list of Range Names for the Alias List, each Name being one rwo of the Alias List and the name being the first name which is in the first column of the alias list. Thus, using my example, the name Mark (and all the others) are contained in a range named Marc.
For the first name psuedo-(alias)-match, I have added a third column to the Criterion range. This column has a blank column heading, unlike the other two which are (obviously) titled Last and State. In the actual cells in the Criteia range for this third column, is an equation of mthe following general form:
=COUNTIF(Marc,'Database'!C8)
where Marc is the name of the range containing the aliases for the first name that's in the to-find list. C8 is the first cell of the database column on the Database sheet where the first names are.
This actually works! But I only if I TYPE IN the range name that contains the first name in the corresponding row of the to-find list. Since there are 35,000 rows in the Criteria range, its impossible to type in all the range names by hand. Especially since both the database and the to-find lists are ever-changing.
I do have a means of looking up, automatically, with equations, the range name which contains the first name in the to-find list. So right next to this column of the criteria range, but not part of the criteria range, is the equation which does this, and it has the word marc as its result.
If I could figure out some way of transferring the equation result Marc into the COUNTIF function of the equation in the criteria range, I'd have a solution!
I have tried indirect addressing and vlookup to do this. Nothing works except directly typing the name Marc into the COUNTIF formula.
Anybody got any other ideas? Its been a real brain teaser that I have been struggling with for two weeks.
I would perfer NOT to use a macro, the only reason being that I am not very good at writing macros and it would take me forever to figure out this one. I am, however, and expert at equations. There may be an entirely different approach to this (still excluding macros), and if anyone has any clever ideas, I'd waiting to hear them!
Hope you understand the question, its somewhat hard to explain without showing the spreadheet (which is 127 meg in size!).
Thanks for your thoughts, hopes, ideas and prayers.
>>>> Bill
