Align two ranges of data

dim4x4

Board Regular
Joined
May 8, 2002
Messages
108
Hello!

I have two tables of data ~2000 rows each, with mostly intersecting full names. I would like to align these two tables by full names to make a combined third table. Please see an example. I want from tables 1 and 2 to make a combined table 3.

!Combined.xlsm
ABCDEFGH
1TABLE 1TABLE 2
2
3Full NameEmailCompanyOtherFull NamePhoneOther
4Alec Blackablack@example.comAAlec Black111
5Ben Whitebwhite@example.comBCheryl Smith222
6Cheryl Smithcsmith@example.comCFlorence Night333
7David Bowiedbowie@example.comDStewart Long444
8Edward Niceenice@example.comE
9Florence Nightfnight@example.comF
10
11
12TABLE 3
13
14Full NameEmailCompanyOtherFull NamePhoneOther
15Alec Blackablack@example.comAAlec Black111
16Ben Whitebwhite@example.comB
17Cheryl Smithcsmith@example.comCCheryl Smith222
18David Bowiedbowie@example.comD
19Edward Niceenice@example.comE
20Florence Nightfnight@example.comFFlorence Night333
21Stewart Long444
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
Assuming that we can match on the "Full Name" function, I think what you can do is use the VLOOKUP formula to get what you want.
See: MS Excel: How to use the VLOOKUP Function (WS)

I would first copy the Full Name columns from the two tables, and paste them, one under the other, and then sort the list.
Then use Excel's built-in "Remove Duplicates" functionality to weed out the duplicates.
Then use the VLOOKUP function for each field, looking into each table.

You can handle the errors that are caused when records don't match by wrapping them in an IFERROR function, i.e. use a formula structure like this:
Excel Formula:
=IFERROR(VLOOKUP(...),"")
 

dim4x4

Board Regular
Joined
May 8, 2002
Messages
108
Hi Joe4! Thank you for the answer, but I have a hard time following your suggestions. Could you please post an example? Thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
Hi Joe4! Thank you for the answer, but I have a hard time following your suggestions. Could you please post an example? Thank you!
What part is confusing to you?
Copying/pasting data?
Sorting data?
Removing duplicates?
Creating VLOOKUP formulas?

Hoping that you have at least a basic baseline level of Excel knowledge that we can work from.
 

dim4x4

Board Regular
Joined
May 8, 2002
Messages
108

ADVERTISEMENT

I copied and pasted data, sorted, and removed duplicates. Got a column like this:

Full Name
Alec Black
Ben White
Cheryl Smith
David Bowie
Edward Nice
Florence Night
Stewart Long

What do I do with this column? How do apply a VLOOKUP to it? Thank you!

PS. I know how to use VLOOKUP when there is a contiguous table.
PSS. Please give me a second, I think I'm getting what you mean.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
What do I do with this column? How do apply a VLOOKUP to it? Thank you!
OK, great. That's a good start. Then add the column headers going across for all the columns you want/show in your last image.

In order to help you with thre VLOOKUP functions, please provide me with the following information:
1. What is the exact sheet name and range of your Table1 data?
2. What is the exact sheet name and range of your Table2 data?
3. What is the exact sheet name and range of this new sorted list you just created?

I am asking because I have a suspicion that the example you posted in the first post is oversimplified. If I were to program according to that, you would need to understand how the functions work in order so you could adjust the formulas in to be able to get them to work for you. If you tell me the real, actual details, we should be able to come up with formulas that are ready to "plug-and-play".
 

dim4x4

Board Regular
Joined
May 8, 2002
Messages
108

ADVERTISEMENT

I think I got most of it. Thanks for your help! Here are the answers:

1. Sheet name: "Compare". Range of Table 1: A2:C1997. Full names in A2:A1997.
2. Sheet name: "Compare". Range of Table 2: E2:H1988. Full names in E2:E1988.
3. Sheet name: "Compare". Range of the sorted list: A2000:A4180.

One more question. Some of the full names in tables 1 and 2 are slightly different, but it is actually the same person. For example, in table 1 "Abel Alexandre", in table 2 "Abel Martin Alexandre". It's actually the same person, but the remove duplicates function left both instances. Is there a way to leave just one instance using VLOOKUP's last parameter TRUE/FALSE. Or I'll just have to weed those out one by one? Thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
OK, for columns B and C (email and company), place these formulas in the following cells:
B2000: =IFERROR(VLOOKUP($A2000,$A$2:$C$1997,2,0),"")
C2000: =IFERROR(VLOOKUP($A2000,$A$2:$C$1997,3,0),"")
and copy down for all rows.

The size of the second argument of VLOOKUP should be as follows:
- it should start with the column you are matching on (column A), which should always be the left-most field in the range
- it should include ALL the possible columns that you want to return data from
- it should all the rows you want to search

Note the only difference between the two is the third argument. The third argument is tied to the second argument.

That says, in the range of $A$2:$C$1997, return the value from the 2nd column (when the third argument is 2).
Note that this third argument can never exceed the number of columns in the second argument.
So, we could not have the third argument be 4, since there are only three columns in the second argument (columns A-C).

For your phone number, to be looked up in the second table, you did not indicate which of the four columns it is located in (E, F, G, or H). Let's say it is in column G. Then the formula to put in cell D2000 to return the phone number would look like:
=IFERROR(VLOOKUP($A2000,$E$2:$H$1997,3,0),"")
since column H is the third column or our lookup range (columns E:H).

So, you should be able to add any other columns you want to return in the same manner, using the same formula structure.
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
One more question. Some of the full names in tables 1 and 2 are slightly different, but it is actually the same person. For example, in table 1 "Abel Alexandre", in table 2 "Abel Martin Alexandre". It's actually the same person, but the remove duplicates function left both instances. Is there a way to leave just one instance using VLOOKUP's last parameter TRUE/FALSE. Or I'll just have to weed those out one by one?
That is probably going to be problematic. VLOOKUP's last parameter really works best for numeric entries, not Text ones. You could get errenous results trying to use it on Text fields (unmatched records lining up with the wrong value). I would NEVER try to use the approximation argument on text fields!

Quite frankly, names are a horrible option to match on, as they can often be written in many, slightly different ways. And they are not always necessary unique (i.e. you could have two "John Smiths". I usually only try to match on them as a last resort, and try to use some sort of ID number or some other unique value, if at all possible.

If both data tables were being generated from the same system, so that you knew the names were not going to have variation, that would be optimal. If not, unless you have a way of fixing/standardizing them before matching on them, you are going to probably have a lot of false unmatched records that you will need to clean-up at the end.
 

dim4x4

Board Regular
Joined
May 8, 2002
Messages
108
Thank you very much, Joe4! VLOOKUP is rather simple, I should have figured it out by myself. But the initial method of combining two tables that you proposed, I would have never come up with it.

Again, thank you very much for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,135
Messages
5,640,318
Members
417,136
Latest member
reeton3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top