• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

Excel merging data or lookup

I have 2 spreadsheets that I need to "grab" data from one of them in to the other.

One spreadsheet (1) has contacts with their addresses (8 columns: A = Name, B = Company, C = Street Address, D = Street Address 2, E = City, F = State, G = Zip, H = Country)
The other spreadsheet (2) has contacts with the number of shares. (2 columns: A = Name, B = Shares)
The Names in the spreadsheets may not be identical, but they are similar. Is there a way to pull with an "If" statement where Spreadsheet (1).Name is LIKE Spreadsheet (2).Name... And there will be some Names in one spreadsheet and not the other (this goes both ways).

Is there any way to "grab" the address info from spreadsheet (1) and place in spreadsheet (2)?

I have tried vlookup, but it was pulling incorrect data. Looked as if it was only grabbing the first instance of the first name and not matching according to the full name.

Hope that makes sense! Thanks!
0
Crossmatch
Asked:
Crossmatch
  • 6
  • 4
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Do you always have the same number of contacts in both sheets?

Can you insert an ID field in column A (formula = Row()) that you would be able to do a vlookup on?
0
 
Dave BrettVice President - Business EvaluationCommented:
>The Names in the spreadsheets may not be identical, but they are similar.
how similiar?
an example would help
Cheers
Dave
0
 
CrossmatchAuthor Commented:
There is not the same amount of contacts.. There are some duplicate contacts.

I should probably look at getting rid of the dups first huh?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CrossmatchAuthor Commented:
Here is an example of what I mean by similar:

Spreadsheet (1) - Barbara & Ronald Shiffler

Spreadsheet (2) - Barbara and Ronald E. Shiffler

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://www.ahut.edu.cn/yxsz/ahkl/Teaching/Excel%20for%20Chemists/Ch06.pdf

you can define the range as a db and use the * (pg 12) . . . but it's going to be a fuzzy search because what distinguish the above from

Barbara and Mr. Smith
vs
Barbara Williams
vs
Barbara Walters

even a like may get these confused.
0
 
CrossmatchAuthor Commented:
ged325,
Do you know how to Set Database in Excel 2007 or 2003? in the pdf you sent, it says (available in Excel 97 only).
Thanks.
0
 
CrossmatchAuthor Commented:
I was thinking of importing in to Access or SQL if we are unable to do this in Excel.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
DB for excel 2003:
http://www.macalester.edu/its/documentation/classes/excel2003dbclass.pdf

before going forward with that I would recommend examining your search paramaters.  Again, what distinguishes the above, how much of a match is a full match?
0
 
CrossmatchAuthor Commented:
I feel like a match would be at least the first name and last 2 initials. For example, Barbara Wa....

Is there any way to tell it to find as much of a match as possible? I've never heard of anything like that, but thought I'd ask.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Not that I'm aware of in terms of matching.

Off the top of my head though I would go character by character and do something like this:

1) start at the beginng of both strings
2) process the characters
3)  while equal correctchar +=1
4) if different go through the longer string until characters are = again.
5)  Do until you reach the end of a string.

Percentage of correct characters:
correctchar * 2 / (len(string1)+ len(string2))  * 100  

if formula > threshold  then match.

Again, you'll need to parse each string though.
0
 
CrossmatchAuthor Commented:
Thanks for all the help. I'm just going to import in to a Access Database and work off of that.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now