Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging worksheets (Excel 2000 sr-1)

    I have a user who needs to combine or merge two worksheets. He received one worksheet from human resources and the other from the state - the one from HR is more current and he needs to take that worksheet and add it to the state worksheet. The problem is that the state lists certain employees, say 1-1000 and they are sorted by SS#s. The HR lists new employees also in order by SS#s. How can you insert the new employees - into the state worksheet and keep the order the same. The state worksheets have blank columns that I want to populate with the HR info. I know this is hard to understand or maybe I just cannot explain it correctly. I looked at sharing documents and accepting and rejecting revisions, but the documents are NOT two versions of the same file. They are two separate workbooks.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    Can't you just add the new records to the end and then sort them to the right order?

    Peter

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    OK - I knew I didn't explain this correctly. Two separate worksheets, let's call them master sheet and update sheet. The master sheet has everyone's name, rank, SS#, etc up to 10 columns, some of the columns are blank and will be populated by the update sheet. Once a week the state sends a new updated sheet with new employees and all their info. This info needs to be put into the master sheet but the columns do not match up. The existing employees are already on both the master and update sheet so if you paste each column, you get two of the same person. I will think about how to explain it better. thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    It sounds this will require a macro to accomplish. However, it will be almost impossible to write a macro without seeing what the worksheets look like. Could you create a dummy workbook that has two worksheets that look like what you have and that have some dummy data in them and attach that workbook to a reply to this message?
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    ok - I took out the personal information. thank you - I appreciate anything you can do. I guess I have to attach each workbook separately?
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    here is the update workbook
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    OK, I got the workbooks and I see the general idea of what you want to do. However, I still have a few questions:

    1- Column A in both workbooks appears to be an ID number of some kind. However, in the Master workbook the values in this column are text and in the Update book they are numbers. Is this the way it will be in the real workbooks?

    2- In these workbooks, it looks like column B in the Master and Column C in the Update book contain a name. However, in these workbooks, the names for the same ID number do not match. What do you want to happen when the master is updated from the Update book?

    3- In the Update book that you sent, row 10 contains an ID number, but the other cells on this row are empty. What should the update do with this ID?

    4- Column E in the Update book and Column C in the Master look like they might be the same information. What should the update do when the information in the update book is different from that in the Master?

    5- In the Update book, columns B, D, F, G, H, and I do not appear to be in the Master. Should the update just add them as columns F, G, H, I, J, and K?

    6- The update book you sent did not have any IDs that were not in the master. Is this how it will always be? If not, what should happen if there is an ID that is not in the Master? Just add it to the end of the Master?
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    Legare, thank you so much for your time. Over the weekend, the user imported into Access and linked the tables on the SS# and then exported back to excel. I never thought to have him try Access. Learn something new everyday.

    Thanks

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    Ok, glad you got it solved. In case you need to do this often and would rather use an all Excel solution, I am posting the macro I wrote last Friday. It does not take into account the questions I asked in my previous quote.

    <pre>Public Sub UpdateMaster()
    Dim strWk As String
    Dim oUpdateBook As Workbook, oUpdate As Worksheet, oMaster As Worksheet
    Dim I As Long, J As Long, K As Long
    Dim vFileName As Variant
    Application.ScreenUpdating = False
    vFileName = Application.GetOpenFilename(Title:="Select Update File")
    If vFileName = False Then Exit Sub
    Workbooks.Open Filename:=vFileName
    Set oUpdateBook = ActiveWorkbook
    Set oUpdate = oUpdateBook.Worksheets("Sheet1")
    Set oMaster = ThisWorkbook.Worksheets("Sheet1")
    I = 0
    While oUpdate.Range("A1").Offset(I, 0).Value <> ""
    J = 0
    While oMaster.Range("A1").Offset(J, 0).Value <> "" And _
    oMaster.Range("A1").Offset(J, 0).Value <> oUpdate.Range("A1").Offset(I, 0).Value
    J = J + 1
    Wend
    oMaster.Range("A1").Offset(J, 0).Value = oUpdate.Range("A1").Offset(I, 0).Value
    oMaster.Range("A1").Offset(J, 1).Value = oUpdate.Range("A1").Offset(I, 2).Value
    oMaster.Range("A1").Offset(J, 2).Value = oUpdate.Range("A1").Offset(I, 4).Value
    oMaster.Range("A1").Offset(J, 5).Value = oUpdate.Range("A1").Offset(I, 1).Value
    oMaster.Range("A1").Offset(J, 6).Value = oUpdate.Range("A1").Offset(I, 3).Value
    oMaster.Range("A1").Offset(J, 7).Value = oUpdate.Range("A1").Offset(I, 5).Value
    oMaster.Range("A1").Offset(J, 8).Value = oUpdate.Range("A1").Offset(I, 6).Value
    oMaster.Range("A1").Offset(J, 9).Value = oUpdate.Range("A1").Offset(I, 7).Value
    oMaster.Range("A1").Offset(J, 10).Value = oUpdate.Range("A1").Offset(I, 8).Value
    oMaster.Range("A1").Offset(J, 11).Value = oUpdate.Range("A1").Offset(I, 9).Value
    I = I + 1
    Wend
    oUpdateBook.Close
    Set oUpdateBook = Nothing
    Set oUpdate = Nothing
    Set oMaster = Nothing
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging worksheets (Excel 2000 sr-1)

    Thank you SO much. You are wonderful! I cannot believe how much people like you and others go out of their way to help others.

    Have a great week!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •