Solved

Merging and sorting data sheets from Excel

Posted on 2007-11-28
18
1,608 Views
Last Modified: 2012-06-22
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,

henrik
0
Comment
Question by:henrikatwork
  • 10
  • 7
18 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 20366765
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]
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 20366783
you can start with this

Sub getAllSheet()
Dim xlObj As Object, xlPath, xlFile, j,strWSname,sTable
sTable="Table1"
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 & "!"


        Next
    End With
    xlObj.Quit
    Set xlObj = Nothing
End Sub

now you can merge the 2 tables

post the excel file here if you need more assistance  www.ee-stuff.com


0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20366799
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:henrikatwork
ID: 20366891
capricorn1: How do I use that script, and do I need to modify it somehow to make it work with my Excel file?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20366909
you have to place those codes in the Access db form's module or standard module
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20367332
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20367375
it will be better if you zip your db and excel file and upload here www.ee-stuff.com
so we will be looking and talking at the same file.
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20367493
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20367504
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20367716
<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
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20368095
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.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 20368214
try this
https://filedb.experts-exchange.com/incoming/ee-stuff/5865-Budgeted_parts_list.zip

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



0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20368694
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20368723
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.
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20368821
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)?

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 20369423
<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
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20372340
Thank you very much, I will try this in a few hours and ask again if I have any additional questions.
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 20375630
I believe I got everything to work exactly like it should now. Thank you very much for your quick and thorough help!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question