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

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

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]

...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

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.