You are here:

Excel/Merging Worksheets

Advertisement


Question
QUESTION: Hello Damon,

I make comments in a cell at the end of a row of data monthly.  Some rows will carry over to the next month when I receive a new data sheet, some will be deleted, and new data rows will be added in.  Some cells within the rows will remain constant each month, but one cell within the row will change every month.  I would like to know if there's a way to merge my OLD worksheet (preserving row color coding and my comment cell) to the NEW worksheet without changing the variable data cell.

An example:  Old worksheet:

John Doe       45142      $450      Pending X
Jack Doe       48742      $212      Pending Y
Jane Doe       49722      $313      Pending Z

New worksheet:

John Doe       45142      $463
Sam Doe        48999      $700
Jane Doe       49722      $322

When I merge the Old worksheet to the New worksheet (or vice-versa), I want to keep all data cells the same, but add in my comments to the rows that carried over to the new month (and row color coding if possible).  The only unique identifiers from one month to the next, would be the first 2 columns, being name and ID number.  In the example above, I lost one row and added another; all remains equal with the exception of column 3, which changes from month to month.

Thanks in advance for any help.

Barron

ANSWER: Hi Barron,

Here is a macro that I believe does what you describe that you want.
_____________________________________________________________

Sub MergeOld2New()
  Dim OldWS      As Worksheet
  Dim NewWS      As Worksheet
  Dim OldID      As String
  Dim Orow       As Long  'row index in Old worksheet
  Dim Nrow       As Long  'row index in New worksheet
  Dim LastNrow   As Long  'last data row in new worksheet
  
  'Assume worksheets are named "Old" and "New"
  Set OldWS = Worksheets("Old")
  Set NewWS = Worksheets("New")
  
  With NewWS
     LastNrow = NewWS.Cells(65536, "B").End(xlUp).Row
     For Orow = 2 To OldWS.Cells(65536, "B").End(xlUp).Row
        OldID = Trim(OldWS.Cells(Orow, "B"))
        For Nrow = 2 To LastNrow
         If Trim(.Cells(Nrow, "B")) = OldID Then
         'OldId matches ID in New worksheet
         'copy comment cell from old to new
         .Cells(Nrow, "D") = OldWS.Cells(Orow, "D")
         'copy fill color of old ID cell to new row
         .Range(.Cells(Nrow, "A"), .Cells(Nrow, "D")).Interior.Color = _
         OldWS.Cells(Orow, "B").Interior.Color
         Exit For
         End If
        Next Nrow
        'New worksheet does not contain current row in Old worksheet (OldID)
        'so go to next Orow
     Next Orow
  End With
  
End Sub
________________________________________________________

Note that I assumed your worksheets were named "Old" and "New". If they are named something else simply edit the names I assigned in the code (2 places).

I also assumed that row 1 on both sheets is a header row and that the data start in row 2. If you have no header rows, or more than 1, you can edit the starting indices (which currently = 2) of the Orow and Nrow loops accordingly.

I further assumed that the four data values (Name, ID, $amount, and comment) in the two sheets reside in columns A:D.  I have also assumed that it is just these columns that you have color-coded in the Old worksheet, not entire rows, and that all four columns are colored the same.

To install this macro in your workbook go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste my code into the Code pane.  To run the macro go back to Excel and Alt-TMM.

Feel free to follow up if I have misinterpreted your question in any way.

Damon


---------- FOLLOW-UP ----------

QUESTION: I've been using this macro for several months and it's been MOSTLY successful however I was wondering if there was a way to improve upon it.  First off, the color coding is by row and not by column, however that typically transfers over on the new sheet when I merge them.  The main issue is when there are near duplicate entries, the comments column (and color coding) only transfers on the FIRST entry, not all others for some reason.  For example:

John Doe       45142      $450      Pending X
John Doe       45555      $900      Pending X
John Doe       41111      $800      Pending X

in the example above, the new spreadsheet will retain all of the same values with the exception of the dollar amount.  The 4th column, the comments column, is something that I add in to keep notes attached to a specific person, and it's common as to all entries for that person.  After merging the spreadsheets in the example above, I would be left with a non-color coded version like this:

John Doe       45142      $450      Pending X
John Doe       45555      $900   
John Doe       41111      $800

I typically spend only about an hour each month correcting each new spreadsheet after I merge them, but if there is a fix for this I would GREATLY appreciate it!   Thanks in advance!

Barron

Answer
Hi Barron,

I've made a few modifications to the code in an attempt to correct the issues you are having, which I think might be due to non-printing characters in the ID number cells causing a failure to match what looks like the same cell on the other sheet.  

I'm not sure what you meant by "near duplicate" entries. Should I be testing for some sort of "approximate" matches?  The numbers you show match exactly, or not at all.

Regarding the color coding by row, not column, I simply colored the entire matching row (columns A:D) on the New sheet based on the coloring of the ID cell of the Old sheet.  I'm guessing this is what you want.

Well, I hope this code solves your problem.

__________________________

Sub MergeOld2New()
 Dim OldWS      As Worksheet
 Dim NewWS      As Worksheet
 Dim OldID      As String
 Dim Orow       As Long  'row index in Old worksheet
 Dim Nrow       As Long  'row index in New worksheet
 Dim LastNrow   As Long  'last data row in new worksheet
 
 'Assume worksheets are named "Old" and "New"
 Set OldWS = Worksheets("Old")
 Set NewWS = Worksheets("New")
 
 With NewWS
    LastNrow = NewWS.Cells(65536, "B").End(xlUp).Row
    For Orow = 2 To OldWS.Cells(65536, "B").End(xlUp).Row
       OldID = Trim(CleanString(OldWS.Cells(Orow, "B")))
       For Nrow = 2 To LastNrow
        If Trim(CleanString(.Cells(Nrow, "B"))) = OldID Then
        'OldId matches ID in New worksheet
        'copy comment cell from old to new
        .Cells(Nrow, "D") = OldWS.Cells(Orow, "D")
        'copy fill color of old ID cell to new row
        .Range(.Cells(Nrow, "A"), .Cells(Nrow, "D")).Interior.Color = _
        OldWS.Cells(Orow, "B").Interior.Color
        Exit For
        End If
       Next Nrow
       'New worksheet does not contain current row in Old worksheet (OldID)
       'so go to next Orow
    Next Orow
 End With
 
End Sub

Sub CleanSelectedRange()
  '  "Cleans" contents of all selected cells on the active worksheet
  Dim Cell    As Range
  For Each Cell In Selection
     If Not Cell.HasFormula Then
        Cell.Value = CleanString(Cell.Value)
     End If
  Next Cell
End Sub

Function CleanString(StrIn As String) As String
  '  "Cleans" a string by removing embedded control (non-printable)
  '  characters, including carriage returns and linefeeds.
  '  Does not remove special characters like symbols, international
  '  characters, etc. This function runs recursively, each call
  '  removing one embedded character
  Dim iCh        As Integer
  CleanString = StrIn
  For iCh = 1 To Len(StrIn)
     If Asc(Mid(StrIn, iCh, 1)) < 32 Or Asc(Mid(StrIn, iCh, 1)) = 160 Then
        'remove special character
        CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
        Exit Function
     End If
  Next iCh
        
End Function
_______________________________________________________

Feel free to follow up again and even email me a sample of your two sheets at VBAexpert@myway.com if there is still a problem.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.