Reconciling and merging 2 tables with almost identical 1st two columns?

Hi,

I have 2 Excel tables with information about a datamodel. (Excel 2003, by the way)
The sheets have almost the same 1st two columns: data table name and data column name, and then different columns for different information.
Table #1 with 2 extra columns indicating foreign key and comment and table #2 with 2 extra columns indicating data type and data lenght.
Most rows (identified by data table name and data column name) are in both tables; however some rows are only in one table.
I want to reconcile and merge those 2 tables into a single sheet with
- all 8 columns and with one single row when a match can be found between table #1 and table #2 (based on identical data table name and data column name)
- and, in between, the rows from either tables, which haven't found a match in the other table
- all the while keeping the order by (first data table name and second data column name), which both tables already have.

I hope I wasn't too complicated in my explanations ;-)
Can anyone help me? Thx a lot!

Originally Posted by dmaurer

Hi,

I have 2 Excel tables with information about a datamodel. (Excel 2003, by the way)
The sheets have almost the same 1st two columns: data table name and data column name, and then different columns for different information.
Table #1 with 2 extra columns indicating foreign key and comment and table #2 with 2 extra columns indicating data type and data lenght.
Most rows (identified by data table name and data column name) are in both tables; however some rows are only in one table.
I want to reconcile and merge those 2 tables into a single sheet with
- all 8 columns and with one single row when a match can be found between table #1 and table #2 (based on identical data table name and data column name)
- and, in between, the rows from either tables, which haven't found a match in the other table
- all the while keeping the order by (first data table name and second data column name), which both tables already have.

I hope I wasn't too complicated in my explanations ;-)
Can anyone help me? Thx a lot!

Hi,

as I'm not really clever at comprehension I seem to have missed two columns.

Column A and B are the 'key' columns, and exist in both sheets.
There are '2 extra columns indicating foreign key and comment'
Then there are '2 extra columns indicating data type and data lenght'
which represent 'all 8 columns'


well, I've lost two columns, but for your abbreviated data the Macro

Please Login or Register  to view this content.

should make a 6 column version.

note, there is no specification as to which column is which, if the second table (Sheet3) is A:D then insert two columns and you will not need to change the sub.

Let me know how you go
---
amended to allow for last row in file 2 addition.
---

Originally Posted by dmaurer

Hi Bryan,

And thank you for your answer and your piece of code!

Sorry about the misunderstanding about the columns, here is the explanation about the 8 colums:

- table #1 has 4 columns: A and B as "key" columns and C and D for extra information
- same for table #2: A and B as "key" columns and C and with some other extra information

when I said 8 columns I meant all columns from both tables:
A, B, C and D from table #1 and then A, B, C and D from table #2, therefore up to column H in the new "merged" table (#3)
If the same combination of keys can be found in both table, then it means there will be a fully filled row in table #3 with obviously the same information in columns A /B and E /F
If a combination of keys can only be found in table #1 then it means a half-filled row in table #3 with only columns A to D filled in
Similarly if a combination of keys can only be found in table #2 then it means a half-filled row in table #3 with only columns E to H filled in
But still respecting the alphabetical order of table/column for columns A and B and E and F in table #3.

Let me know if it's still unclear.

I had hoped there would be some built-in tool in Excel 2003 to deal with this issue but since you sent me VB code I guess there's no such tool?

I'm not very familiar with macros but I'll try to understand what you sent and use it.

Thanks a lot.

OK on the columns, the easy way is Tools, Options, Macro, Macros, insert a name myMacro and Create
A window in presented,

Sub mymacro()

End Sub

copy the code into there (you need only one sub and ome End, exclude those from the copy)

On the second sheet, insert two columns, (data is then A B - - E F) and run the macro.

the first sheet A B C D will be joined to A B - - E F to give
A B C D E F


you can change

sh2 = "Sheet2"
sh3 = "Sheet3"

to your own sheetnames.

Let me know how you go
---