# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Merge two tables

## avveerkar

I have two tables. first table on sheet1 has two columns- first for Employee Number and second for Employee Age. Second table, say on sheet2, again has two columns- first Employee Number and second Employee Salary. Both tables are sorted on Employee Number. I want to create a new table of three columns, say on sheet3, with first column as Employee Number, second as Employee Age and third as Employee Salary. First two tables may have some Employee Numbers common and some numbers may not be common. Third table that we create must list all employee numbers (without repeating the  Employee Numbers common to two tables) and have  both age  and salary columns filled up where data is available. For example if first table has 10 entries and second also has 10 entries but only 7 have common emp number then the new table will have 13 entries as there are total 13 employee numbers between the first two tables. Out of these 13entries 7 will have both age and salary columns filled up, 3 will have only age and 3 will have only salary. In short I want to merge two tables to create a third table which will have all the data of two tables without a duplicate entry.
What wold be easiest way to handle this?
A V Veerkar

----------


## BradC

This task is well suited for a Pivot Table, which would give you the results you want without repeating duplicate employee numbers.

----------


## avveerkar

Thanks a lot. Pivot tables is something that I have not really used so far. Let me first study this and I will come back if I still have the problem.
A V Veerkar




> This task is well suited for a Pivot Table, which would give you the results you want without repeating duplicate employee numbers.

----------


## avveerkar

> This task is well suited for a Pivot Table, which would give you the results you want without repeating duplicate employee numbers.



OK I tried Pivot Tables but I could not manage. I need help. To explain my problem say there are men with names AAA, BBB, CCC etc. They stay in cities NY, Phil etc and have cars Honda, GM etc. I have two lists. one lists names with cities and the other names with cars. Using these as source I want to generate a third list with names, cities and cars.
List1
NAME	CITY
AAA	NY
BBB	Chicago
DDD	Phil
EEE	NY
FFF	Chicago

List2
NAME	CAR
BBB	Honda
CCC	Toyota
DDD	GM
FFF	Honda
PPP	GM

List to be created

NAME	CITY	CAR
AAA	NY	
BBB	Chicago	Honda
CCC		Toyota
DDD	Phil	GM
EEE	NY	
FFF	Chicago	Honda
PPP		GM

I tried pivot tables with multiple lists but instead of showing the names of city and car I get value count like this

NAME	CITY	CAR
AAA	1	
BBB	1	1
CCC		1
DDD	1	1
EEE	1	
FFF	1	1
PPP		1


That is not what I want. I know there must be a very simple solution but it beats me. Please help.

And since we are at it, I would also like to know how can I show tables in my post. The examples I have shown above would look better and would have been much easier to understand if they could be shown in tabular form. I am not able to do that. I created lists in EXCEL and copy/pasted them here but when I submit the post the tabular formatting is lost. I know it can be done because many posts have the data in a table format. Please tell me how to do this.

Veerkar

----------


## avveerkar

I really need help on this. I will repeat my problem. I want to merge first two tables to give output as shown in third table




```
Please Login or Register  to view this content.
```


Veerkar

----------


## avveerkar

OK, I guess that this cannot be done so easily in EXCEL. Meging two tables (lists) is a demanding DBA function. There are third party add-ons available for EXCEL to achieve this task.
A V Veerkar

----------


## Gabor

This is to pull over matching data from another sheet in case a reference data element is the same. This is not exactly what you wanted, but close (it does't create a new sheet). You can probably build on this.
Cheers, Gabor

Public Sub PullOverMatchingData()
Application.ScreenUpdating = False
Dim ToList, FromList, RefData, MyData
Dim i As Integer, j As Integer
Dim FRcol As Integer, FRrefCol As Integer, FRcmt As Integer
Dim TOcol As Integer, TOrefCol As Integer, TOcmt As Integer

FRcol = 2        '# of the column you want to copy over
FRrefCol = 1     '# of the column with the reference data on the FromSheet
FRcmt = 4        '# of the column where you might want to place a comment on the FromSheet
TOcol = 3        '# of the column where you want to copy stuff over on ToSheet
TOrefCol = 1     '# of the column with the reference data on ToSheet
TOcmt = 4        '# of the column for any comments on the ToSheet

ToList = "Sheet1"   ' Enter here the Name of the sheet you want to pull data over
FromList = "Sheet2"  ' Enter here the Name of the sheet you want to pull data freom

Sheets(FromList).Select

For i = 2 To 20000
    If Cells(i, FRrefCol) = Empty Then GoTo 999
    MyData = Cells(i, FRcol)
    RefData = Cells(i, FRrefCol)
    Sheets(ToList).Select
       For j = 2 To 20000
           If Cells(j, TOrefCol) = RefData Then
                Cells(j, TOcol).Formula = MyData
                Cells(j, TOcmt).FormulaR1C1 = "data pasted" '<=UPDATE STRING
                Sheets(FromList).Select
                Cells(i, FRcmt).FormulaR1C1 = "data copied" '<=UPDATE STRING
                GoTo 100
            ElseIf Cells(j, TOrefCol) = Empty Then
                Sheets(FromList).Select
                Cells(i, FRcmt).FormulaR1C1 = "not found" '<=UPDATE STRING
                GoTo 100
            Else
            End If
        Next j
100 Next i
999    Application.ScreenUpdating = False
Sheets(ToList).Select
End Sub

----------


## avveerkar

> This is to pull over matching data from another sheet in case a reference data element is the same. This is not exactly what you wanted, but close (it does't create a new sheet). You can probably build on this.
> Cheers, Gabor
> 
> Public Sub PullOverMatchingData()
> Application.ScreenUpdating = False
> Dim ToList, FromList, RefData, MyData
> Dim i As Integer, j As Integer
> Dim FRcol As Integer, FRrefCol As Integer, FRcmt As Integer
> Dim TOcol As Integer, TOrefCol As Integer, TOcmt As Integer
> ...



Thanks Gabor. Let me check it tonight. I will come back.
Veerkar

----------


## avveerkar

Thanks Gabor. 
That works. I made some changes to suit my requiremets.

I was wondering if there is some other way ( by using Pivot Tables or Consolidate or something) this can be done in a general way. The way we have done in VBA is very specific to my application. We will have to modify the code if table structures or location of the lists cahnges.

Thanks again for your help
A V Veerkar

----------


## steven1001

another option..
Assume your data is in a spreadsheet called WORK.
In a separate worksheet called "TEMP" use >data>get external data>create new query and create a query that works like:
select Name from WORK:Sheet1
UNION
select Name from WORK:Sheet2

[use msquery to get only the rows in the first sheet, click on the SQL button to see the syntax of the query, manually add the union command and the second select statement]

...This will give you a list of unique names from both WORK worksheets in TEMP. Close the TEMP file.

In a new worksheet in WORK use the data>get external data>create new query to retrieve the combined list from TEMP.  Then use vlookup to populate the next 2 columns with city and car.

It is a pity that excel forces you to do the union query in another worksheet but I have never figured out how to get round this.

regards

----------


## avveerkar

> another option..
> Assume your data is in a spreadsheet called WORK.
> In a separate worksheet called "TEMP" use >data>get external data>create new query and create a query that works like:
> select Name from WORK:Sheet1
> UNION
> select Name from WORK:Sheet2
> 
> [use msquery to get only the rows in the first sheet, click on the SQL button to see the syntax of the query, manually add the union command and the second select statement]
> 
> ...



I am sorry that I didn't notice that you had responded to my query. I am going to try this. But again I am new to "query". But then it is never too late to learn. Let me read some help on this and then try this out.
Sorry again for I missed your reply.

A V Veerkar

----------


## nabberooski

I am doing something similar, but slightly different.  I have two sheets, one with employee info (including SSN for unique ID) and the other with dependent information (each dependent has SSN of the employee they belong to.

Is there a way to have excel insert a row(s) of imported data (dependent info) under corresponding employees?

Any help would be very appreciated, I have 2,500 employees to match dependent records with.

Nathan

----------


## arlu1201

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------

