Merge two tables in a new one

**emarz** · 04-05-2011, 05:42 AM

Hi folks,

can anyone help me in merging two tables into one without using add in or other external software?

Example: I have this two tables:

T1 (my master table)
Name Product Qty
A Z 1
B Y 2
C X 3
D W 4

T2
Name Web
A www.a.com
B www.b.com
C www.c.com
D www.d.com

I'd like a new table with merge data from two two above:

Name Product Qty Web
A Z 1 www.a.com
B Y 2 www.b.com
C X 3 www.c.com
D W 4 www.d.com

Hope I was not confusing.

Thanks for your support

**Kafrin** · 04-05-2011, 05:51 AM

Is this a one-off process? If so, I'd copy the first table and add an extra column with a VLOOKUP formula that looks something like this:

Please Login or Register  to view this content.

...where A is the Name column of the new table and E2:F5 is the web address table.

**Kafrin** · 04-05-2011, 07:55 AM

Oh, meant to add before, if you then want the formula result to be hard-written into the cells, just click on the column, Copy, then Paste Special, Values.

**emarz** · 04-05-2011, 09:10 AM

Originally Posted by Kafrin

Is this a one-off process? If so, I'd copy the first table and add an extra column with a VLOOKUP formula that looks something like this:

Please Login or Register  to view this content.

...where A is the Name column of the new table and E2:F5 is the web address table.

Hi Kafrin thanks for the answer. I tried it but didn't work. I made the example very simple, but the fact is that the two tables are quite different, In fact there are different names, since the master table was implemented, while the other one contain less names.

**Kafrin** · 04-06-2011, 06:21 AM

Well is there any column that matches up in both tables? Or at least almost matches and is different in some consistant way? If not, you'll have problems anyway and it will probably require you to do at least some work manually (e.g. add a matching column to one table).

**emarz** · 04-06-2011, 06:29 AM

Originally Posted by Kafrin

Well is there any column that matches up in both tables? Or at least almost matches and is different in some consistant way? If not, you'll have problems anyway and it will probably require you to do at least some work manually (e.g. add a matching column to one table).

Hi Kafrin

In both tables I have the Coloumn "Name". Names in there are written in the same way in both tables.
In table 1 "Name" is coloumn "B"
In table 2 "Name" is coloumn "C"

I would like Excel to add some info such as website, email address etc that are in Table 2 to Table 1.

**DanExel** · 04-06-2011, 06:45 AM

Seems that Vlookup is stil the answer, or a bit of simle VBA.
Attach your samle sheet and we could solve your problem in no time.

Regards
Dan

**JBeaucaire** · 04-06-2011, 07:14 AM

VLOOKUP() normally can only bring back values to the RIGHT of the Name columns, possibly not ideal. I use INDEX/MATCH for this process so I can target column to MATCH and column to get values from (INDEX) specifically.

For simplicity. let's assume:

T1 is on Sheet1
T2 is on Sheet2

1) In the next empty column on T1 enter a title for the new column in row1

2) In Row2, enter this formula, adjust the MATCH and INDEX ranges to the correct columns from Sheet2:

=INDEX(Sheet2!D:D, MATCH(B2, Sheet2!$C:$C, 0))

3) Copy that formula down the Sheet1 table

4) Copy the new values, then Edit > Paste Special > Values