Merging and sorting data sheets from Excel

The problem is that I need to take data two different Excel sheets (one Excel file) and create a database file with complete information that is sorted accordingly. To exemplify this the first sheet contains a list of items (identified by part reference number) and a supplier, but it does not include the internal part number and the actual name/description of the item. The internal part number and name/description is on the second sheet and is identified with a part reference number.

Sheet 1
Supplier | part reference number | internal part number (empty) | part name/description (empty)
Sheet 2
Internal part number | part name/description | part reference number

Example sheet can be provided if necessary to show a working solution, or if necessary to give a working solution. As a note internal part numbers includes dashes (-) in the number such as 1-1393186-5, and the part reference number also includes letters such as 609885300A.

Now, how can I combine these two sheets and by using Access to combine all the information (supplier, part reference, internal part number and name/description)? The Access needs to automatically sort and match data correctly and combine the two sheets properly. The result of this should be a complete "sheet 1", with all 4 rows of data correctly filled and sorted.

If this question is unclear please ask for more specifics or an example file and I will do my best to provide additional information or try to make it more clear what I am asking for. When explaining please explain step by step how to create a working solution for this problem.

If it makes any difference I have a sheet 3 that does this in Excel where all the data is listed and matched properly that can be shown as an example on what the result in Access should look like.

Best regards,

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can start with this

Sub getAllSheet()
Dim xlObj As Object, xlPath, xlFile, j,strWSname,sTable
xlPath = CurrentProject.Path & "\ExcelFiles\"
xlFile = "Book1.xls"
Set xlObj = CreateObject("excel.application")
    xlObj.workbooks.Open xlPath & xlFile ',, true 'read only
    With xlObj
        For j = 1 To .worksheets.Count
           strWSname= .worksheets(j).Name

         DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                   "" & sTable & "_" & strWSname & "", xlPath & xlFile, True, "" & strWSname & "!"

    End With
    Set xlObj = Nothing
End Sub

now you can merge the 2 tables

post the excel file here if you need more assistance

Are you trying to do this programmatically, or is this a one-time process?

basic Steps:
Import each worksheet into its own table.
Then, create a query which combines the four fields,
 as in
select a.supplier, a.[part reference numbner], b.[internal part number], b.[part name/description] from sheet1 a inner join sheet2 b on b.[part reference number] = a.[part reference number]
henrikatworkAuthor Commented:
This is something that will be done more than once. Think of sheet1 as an invoice of a sort where there are 42 rows of part reference numbers from a supplier that need to get their partname/description and internal part number from sheet 2.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

henrikatworkAuthor Commented:
capricorn1: How do I use that script, and do I need to modify it somehow to make it work with my Excel file?
Rey Obrero (Capricorn1)Commented:
you have to place those codes in the Access db form's module or standard module
henrikatworkAuthor Commented:
I am still a little uncertain of what to do. Could I have a step by step description on how to go about with this from beginning to end? So far I have imported the data into two tables as mentioned earlier.
Rey Obrero (Capricorn1)Commented:
it will be better if you zip your db and excel file and upload here
so we will be looking and talking at the same file.
henrikatworkAuthor Commented:
To clarify the third sheet is an Excel solution on how to merge the two sheets, and can be ignored as I wish to know how to get the same result by adding the data to Access.
Rey Obrero (Capricorn1)Commented:
<the third sheet is an Excel solution on how to merge the two sheets,> are you sure about this ? the part reference  number are not matching in Sheet3
henrikatworkAuthor Commented:
After a closer look I see you are correct. The first solution isnt a solution after all, as it does not fill the function I am looking for. Anyway, as you can see in the first sheet the missing information there is supposed to be taken from sheet 2, so that the list is completed with the internal reference number and name/description. This is the function I am looking for, but after some tests I cannot find a way to make this work in Excel either so I am quite lost in the entire matter of locating the right values and match them properly.
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

place the db in the same folder as the Excel file.
remove sheet3 from the excel file

open the db, click the getExcelFile button
just say Yes to the message box

now see the tables that was created.
Master table contains the four fields with corresponding reference part

henrikatworkAuthor Commented:
From the looks of it this does exactly what I am looking for. Is there any easy way of explaining how you went about and created this solution so I can reproduce it myself if necessary?
Rey Obrero (Capricorn1)Commented:
let us do it this way, examine the database first and ask the question and i will try to answer them as clear as i could.
henrikatworkAuthor Commented:
From the looks of it you have done this:
You created a database, and then added the form with the code/script you posted above. How did you add the form?

After adding the script/code as a form in the database file, the data from the sheets is imported into two different tables. Are the tables created before adding the form/script, and what about the master table? Does the form add these three by itself, or do I need to create them manually somehow?

Lets assume I wish to do the same procedure using a different sheet1 as list, is there any change that must be done in order for the master table to list the new linked/merged sheet? Could this solution be reused by for example having a certain file name for the xls file (or rename the file in the form)?

Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<How did you add the form? >

* Switch to the Database Window. You can do this by pressing F11 on the keyboard.
* Click on the Forms button under Objects on the left side of screen
* Click New
* Click Design View
* Select the command button from the tool bar and drop in the form
henrikatworkAuthor Commented:
Thank you very much, I will try this in a few hours and ask again if I have any additional questions.
henrikatworkAuthor Commented:
I believe I got everything to work exactly like it should now. Thank you very much for your quick and thorough help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.