Align similar worksheet in preparation for merge

[johnmeyer]

I have a typical contact database, which includes address, phone, email, etc.

I then have a separate database which contains only the contact names (last,first) and new, updated emails. I want to update the contact database with these new emails.

However, there are a few problems ...

If you look at the example below, the first three columns (A,B,C) shows an excerpt from the contact database; the next three columns (E,F,G) shows the new emails. What I want to do is move down the rows in this email database, so they match the rows in the contact database. The final three columns (I,J,K) demonstrates what I am trying to achieve.

As you can see, the email list is a much smaller list than the main contact database, with many names missing. Another problem is that the first names are sometimes abbreviated.

Also note that the database can have people with the identical name (both first and last), but different emails. Thus, once the first instance is matched, it is important that the code which does the alignment "keep on moving" so that the next line gets used. If a few lines end up in the wrong place, that's OK, because it can be sorted out when I do the highlighting. I just don't want to have any lines missing.

My ultimate goal is to add a new column in the original database (column D), and put the new email address in that column, but only if it is different than the existing address. Once I have verified the changes, I can easily merge those into column C.

Thanks in advance for any and all help!

Em1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Original Database[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]New Email Addresses[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]New Email Addresses moved to match original names[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Last_Name[/TD]
[TD]First_Name
[/TD]
[TD="align: center"]Email[/TD]
[TD]
[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Email[/TD]
[TD]
[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Abax[/TD]
[TD]Dan[/TD]
[TD]dxyz@mary.com[/TD]
[TD]
[/TD]
[TD]Abax[/TD]
[TD]Daniel[/TD]
[TD]dxyz@mary.com[/TD]
[TD]
[/TD]
[TD]Abax[/TD]
[TD]Daniel[/TD]
[TD]dxyz@mary.com[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Acher[/TD]
[TD]Brian[/TD]
[TD]abc@xyz.com[/TD]
[TD]
[/TD]
[TD]Acher[/TD]
[TD]Brian[/TD]
[TD]abc@xyz.com[/TD]
[TD]
[/TD]
[TD]Acher[/TD]
[TD]Brian[/TD]
[TD]abc@xyz.com[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Adam[/TD]
[TD]John[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Adams[/TD]
[TD]Mary[/TD]
[TD]newemail@abc.com[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Adams[/TD]
[TD]Mary[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]anotheremail@aol.com[/TD]
[TD]
[/TD]
[TD]Adams[/TD]
[TD]Mary[/TD]
[TD]newemail@abc.com[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]myemail@yahoo.com[/TD]
[TD]
[/TD]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]myemail@yahoo.com[/TD]
[TD]
[/TD]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]anotheremail@aol.com[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]anotheremail@aol.com[/TD]
[TD]
[/TD]
[TD]Alabama[/TD]
[TD]Suzy[/TD]
[TD]hi@aol.com[/TD]
[TD]
[/TD]
[TD]Alexander[/TD]
[TD]Max[/TD]
[TD]myemail@yahoo.com[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]Alabama[/TD]
[TD]Suzy[/TD]
[TD]hi@aol.com[/TD]
[TD]
[/TD]
[TD]Allen[/TD]
[TD]Nancy[/TD]
[TD]friendly@abc.com[/TD]
[TD]
[/TD]
[TD]Alabama[/TD]
[TD]Suzy[/TD]
[TD]hi@aol.com[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]Allen[/TD]
[TD]Nancy[/TD]
[TD]friendly@abc.com[/TD]
[TD]
[/TD]
[TD]Amish[/TD]
[TD]Peggy[/TD]
[TD]mymail@compuserve.com[/TD]
[TD]
[/TD]
[TD]Allen[/TD]
[TD]Nancy[/TD]
[TD]friendly@abc.com[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]Aman[/TD]
[TD]George[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]Amish[/TD]
[TD]Peg[/TD]
[TD]mymail@compuserve.com[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Amish[/TD]
[TD]Peggy[/TD]
[TD]mymail@compuserve.com[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]Anadobe[/TD]
[TD]Bob[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]Anders[/TD]
[TD]Larry[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2002

 

[shg]

There's a workbook at https://app.box.com/shared/elrnbidnr7 that will do this, except that you need to move (or copy) the key column to be the leftmost column of each section.

 

[johnmeyer]

Wow, that is so close to what I wanted that it looks like it was written just for me. Many thanks!!

 

[shg]

You're welcome, I'm glad it helped.

 

[johnmeyer]

Spoke too soon. The workbook is password protected, so I cannot get to the code.

[edit] Well, the part that is protected doesn't matter. I found out how to get to the code.

 

[shg]

I never password-protect workbooks, worksheets, or VBA projects -- it would defeat the purpose of posting in a forum.

I did just upload an xlsm version, though -- it's at the same link.

 

[johnmeyer]

Sorry, I was being "thick." The "Mr. Excel" HTML code is password protected. I clicked on that module in the VBA editor and got the error message. My bad.