Align two ranges of data

[dim4x4]

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
	A	B	C	D	E	F	G	H
1	TABLE 1					TABLE 2
2
3	Full Name	Email	Company	Other		Full Name	Phone	Other
4	Alec Black	ablack@example.com	A	…		Alec Black	111	…
5	Ben White	bwhite@example.com	B	…		Cheryl Smith	222	…
6	Cheryl Smith	csmith@example.com	C	…		Florence Night	333	…
7	David Bowie	dbowie@example.com	D	…		Stewart Long	444	…
8	Edward Nice	enice@example.com	E	…
9	Florence Night	fnight@example.com	F	…
10
11
12	TABLE 3
13
14	Full Name	Email	Company	Other	Full Name	Phone	Other
15	Alec Black	ablack@example.com	A	…	Alec Black	111	…
16	Ben White	bwhite@example.com	B	…
17	Cheryl Smith	csmith@example.com	C	…	Cheryl Smith	222	…
18	David Bowie	dbowie@example.com	D	…
19	Edward Nice	enice@example.com	E	…
20	Florence Night	fnight@example.com	F	…	Florence Night	333	…
21					Stewart Long	444	…
Sheet1

 

[Joe4]

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:

=IFERROR(VLOOKUP(...),"")

 

[dim4x4]

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]

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]

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]

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]

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]

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.

 

[Joe4]

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]

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!