• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • 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
 
DaveCommented:
>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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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