Merge duplicate rows, remove missing rows, and keep unique cells. Help please!!!

[jdaugherty]

I'm working on this workbook in Excel 2010. Each Monday I run a query of a backlog that is hundreds of rows long. Many of the rows are the same from week to week but there are always new rows and occasionally one of the old rows will be removed. Ideally, I would like to be able keep one main backlog worksheet. I would then copy and paste the new query into the same workbook and run a macro or VBA or formulas to compare the main backlog worksheet with the new backlog worksheet. It would add any new backlog rows and remove the backlog rows that no longer appear.

Each backlog has 8 columns. 1st column has the date of the backlog (with many similar dates), column 2 has a case # (which may be repeated several times), column 3 has a number from 1 - 15 (which may be repeated several times), column 4 has a letter (which may be repeated several times), column 5 has a location (which may be repeated), column 6 has a name (which may be repeated several times), column 7 has a storage code (which may be repeated), and column 8 has identifying information. Each row is unique but not each column.[TABLE="width: 744"]
<colgroup><col width="93" span="8" style="width:70pt"></colgroup><tbody>[TR]
[TD="class: xl63, align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]case#[/TD]
[TD]Code[/TD]
[TD]Type[/TD]
[TD]Loc[/TD]
[TD]Name[/TD]
[TD]Stocode[/TD]
[TD]ID[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/10/2013[/TD]
[TD]100356[/TD]
[TD]2[/TD]
[TD]E[/TD]
[TD]Downtown[/TD]
[TD]Bob[/TD]
[TD]2E[/TD]
[TD]Marks on ground[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/10/2013[/TD]
[TD]100356[/TD]
[TD]2[/TD]
[TD]E[/TD]
[TD]Downtown[/TD]
[TD]Bob[/TD]
[TD]2E[/TD]
[TD]Striated Marks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/12/2012[/TD]
[TD]100158[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]Millburg[/TD]
[TD]Jen[/TD]
[TD]3rd Flr[/TD]
[TD]Brown box[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

I have something that may be suitable. There are some limitations, though. I have also had to make some guesses.

I have guessed that the query produces the complete set of records but that the additions you have made in your copy of the work sheet need to be added? If that is not a good guess then this may be of no use. If you don't need to keep some extra data you have entered then I can't see why you would need to merge the data. You could just keep the new data.

If that is what you want then I processed the records this way:
Run the query and save the 8 key columns in WS2 (work sheet 2).
Have your permanent records in WS1.
Run the macro to add your extra data in columns after column I to the new query.
The macro matches the 8 key columns and brings through another 7 (as written - but can be changed).

You should now have a complete set of current records. New ones will have appeared and removed ones will no longer be present.

One of the limitations is that I have constructed a large MATCH instruction using sheet names. WS1 works but Sheet1 does not - it it too long! This may be an issue.
The next week you will need to swap the names of WS1 and WS2 and repeat the process.

Sub MERGE()
    Dim LastCol As Long, LastRow1 As Long, LastRow2 As Long
    Dim iRow As Long, jRow As Long
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim myStr As String
    Set WS1 = Worksheets("WS1")
    Set WS2 = Worksheets("WS2")
    LastRow1 = WS1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRow2 = WS2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For iRow = 2 To LastRow2
        myStr = "=IFERROR(MATCH(1," _
                     & "(WS1!$A$2:$A$" & LastRow1 & "=WS2!A" & iRow & ")" _
                    & "*(WS1!$B$2:$B$" & LastRow1 & "=WS2!B" & iRow & ")" _
                    & "*(WS1!$C$2:$C$" & LastRow1 & "=WS2!C" & iRow & ")" _
                    & "*(WS1!$D$2:$D$" & LastRow1 & "=WS2!D" & iRow & ")" _
                    & "*(WS1!$E$2:$E$" & LastRow1 & "=WS2!E" & iRow & ")" _
                    & "*(WS1!$F$2:$F$" & LastRow1 & "=WS2!F" & iRow & ")" _
                    & "*(WS1!$G$2:$G$" & LastRow1 & "=WS2!G" & iRow & ")" _
                    & "*(WS1!$H$2:$H$" & LastRow1 & "=WS2!H" & iRow & ")" _
                    & ",0),0)"
        jRow = Application.Evaluate(myStr)
        ' I is the start of the data after the key and 7 is the number of columns that need adding
        If jRow <> 0 Then WS2.Range("I" & iRow).Resize(1, 7).Value = WS1.Range("I" & jRow + 1).Resize(1, 7).Value
    Next
    
End Sub

This code:

        ' I is the start of the data after the key and 7 is the number of columns that need adding
        If jRow <> 0 Then WS2.Range("I" & iRow).Resize(1, 7).Value = WS1.Range("I" & jRow + 1).Resize(1, 7).Value

specifies which extra columns need keeping. They start in colimn I and there are 7 columns required.