Solved

Merging and sorting data sheets from Excel

Posted on 2007-11-28
18
1,591 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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:henrikatwork
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
you have to place those codes in the Access db form's module or standard module
0
 
LVL 1

Author Comment

by:henrikatwork
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
0
 
LVL 1

Author Comment

by:henrikatwork
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
Comment Utility
<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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now