Compare and separate dataClosed

Question

Hi,

I am obviously new to the forum here, but I came across a question kind of inline with my own on here and wanted to see if I could get some help with my situation.

Original question for reference: http://ccm.net/forum/affich-756470-comparing-two-excel-sheets-and-copying-like-data-to-third-sheet

My particular situation is that I have two sets of data that is updated daily. I need to pull in the data and compare it to the data from the previous day. If entries in the data have a match to the previous day's data or has new entries not found in the previous, it should be moved to a separate sheet. If data in the previous day is not longer showing in the current day's data, it should be moved and added to a running entry list on another sheet (kept for archival report reference).

While my compared entries may be in column A, I need it to copy the entire row that the compared entry is on to the appropriate sheet as there will be more than just one column of data for the entry.

In my proof of concept example I have 4 sheets: 

Shopping_list - sheet used by other reports that contains new and previously existing entries

Current_Requests - datafeed imported entries from today's data

Previous_Requests - entried moved from the Current_Requests before new entries have been imported and compared

Not_Needed - old entries that no longer show up in Current_Requests moved to here for Archival.

Please let me know if this isn't clear enough and I'll try to elaborate as best I know how. Thanks!
System Configuration: Windows / Internet Explorer 11.0

TrowaD · Answer

Hi J_Marley,

Are the values in column A unique? Meaning, can there only be one match?

Best regards,
Trowa

TrowaD · Answer

Hi J_Marley, 

You want to compare Current with Old requests and move all new and existing data to Shopping sheet. Doesn't this mean that everything needs to be moved to Shopping sheet?

Compare Old with Current and move all rows without a match in column A to Not_Needed sheet. For that I wrote this code:

Sub RunMe()
Dim lRow, x As Long
Dim mFind As Range

Sheets("Old_Requests").Select
lRow = Range("A1").End(xlDown).Row

For Each cell In Range("A2:A" & lRow)
    Set mFind = Sheets("Current_Requests").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(cell.Value)
    If mFind Is Nothing Then
        cell.EntireRow.Copy Sheets("Not_Needed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next
    
End Sub

The correct place to put this code is in a module (instead of ThisWorkbook). Go to the top menu > insert > module of the VBA window.

Best regards,
Trowa

Compare and separate data

2 responses

Similar discussions

Subscribe To Our Newsletter!