Combine Two Excel Lists

[Christhemist]

I have two Excel files. One is a DataList and the other is a Matrix. I need to code the Datalist according to the Matrix. So, whatever the values are in the matrix it would populate the Datalist according to the mathcing Offercode. For example.

Here are the data list fields: CustID, First, Name, Address, City, State, Zip, Offercode

Here are the matrix fields: Amount, Date, Expiration, Offercode.

I need the final output to look like this.

(DataList)CustID, (DataList)First, (DataList)Name, (DataList)Address, (DataList)City, (DataList)State, (DataList)Zip, (DataList)Offercode, (Matrix)Amount, (Matrix)Date, (Matrix)Expiration.

To further explain my Vb.net form I have two text boxes and buttons that when pressed browse to the excel files. I then have two comboBoxes that are populated by the headers in the Excel sheets. I want to use to link the tables together. So, like ComboBox1.SelectedItem = ComboBox2.SelectedItem. The theory is that I can load any two lists and map them together as long as the two ComboBox items have the same values within them.

Also, the lists column names will vary moving forward so I need it to select all columns no matter what their header names will be

Not sure how to accomplish this, any help would be great, here is my code thus far:

   Dim xlDirectionLeft As Object = Excel.XlDirection.xlToLeft
    Dim xlDirectionRight As Object = Excel.XlDirection.xlToRight
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer
    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add(DataListFile)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim MyConnection2 As System.Data.OleDb.OleDbConnection
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyCommand2 As System.Data.OleDb.OleDbDataAdapter

        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + MatrixFile + ";Extended Properties=Excel 12.0;")
        MyConnection2 = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DataListFile + ";Extended Properties=Excel 12.0;")

        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand2 = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection2)
        Dim DtSet1 = New System.Data.DataSet
        Dim DtSet2 = New System.Data.DataSet

        MyConnection.Open()
        MyConnection2.Open()

        MyCommand.Fill(DtSet1)
        MyCommand2.Fill(DtSet2)

    xlWorkBook.Save()
    xlApp.Workbooks.Close()
    xlApp.Quit()

[F Scheltens]

Hi. Firstly this topic should be in the "Office development" section of the forum.
I have done some spreadsheets myself and if you have variable names in a cell you should use named ranges to label specific cells or area's. You can then use these ranges in code to refer to a certain cell or cells regardless of content. For example:
worksheets("Sheet1").Range("Column_A_Header")

You can also use dynamic named ranges to refer to the list of items under the header. For example, define a named range to point to the following cells:

=offset($a$2,0,0,counta(a:a)-1)

This will select all the cells in column A which are not empty, and which are in cell A2 or lower.

I would also like to point out that while VBA is possible, it is perfectly possible to do matching like this using just excel formulas:

'use code like this to select the matrix and copy it to a fixed location. This location will be referred to using formulas.
Worksheets("Sheet1").Range("A1:D4").Copy _ 
    destination:=Worksheets("Sheet2").Range("E5")

Create a dynamic named range containing all the rows and columns which contain the values of the matrix.
For this example we will assume that:
- There is a named range "Matrix" which contains the matrix values.
- (Datalist)Offercode is in cell H2.
- (Matrix)Offercode is in the first column of the range "Matrix".

Next, fill cell i2 with the formula:

=index("Matrix", match($h2, offset("Matrix",0,0,rows("Matrix")),0),2)

Notice that the H has a $ in front of it, the number does not. This is needed when copying the formula to other cells.
The match function will search column I for the offercode in H2 and return the rownumber, counting from the starting row of Matrix. The index function will return the value of the cell to the right of the matching offercode.
Copy this formula down.
To copy this formula to the right (Matrix)Date, the Index function should refer to the next column:

'last number incremented
=index("Matrix", match($h2, offset("Matrix",0,0,rows("Matrix")),0),3)

I hope this helps you out, have fun with it!