Compare and separate data

Closed
J_Marley Posts 2 Registration date Wednesday July 19, 2017 Status Member Last seen July 20, 2017 - Jul 19, 2017 at 01:23 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 25, 2017 at 11:06 AM
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!
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 20, 2017 at 11:26 AM
Hi J_Marley,

Are the values in column A unique? Meaning, can there only be one match?

Best regards,
Trowa
0
J_Marley Posts 2 Registration date Wednesday July 19, 2017 Status Member Last seen July 20, 2017
Jul 20, 2017 at 01:50 PM
The values in column A for each sheet are unique to that sheet. They may be duplicated in another sheet, but not on its own.

Here is an example excel doc to show what I am trying to accomplish and how I envision it being set up.

https://www.dropbox.com/s/bmp7qf25f1zbibr/List%20Compare%20and%20separate%20example.xlsm?dl=0

Ultimately, other reports will look for the information in the first sheet for "live" information, while still utilizing the last sheet for running reports that require legacy information.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 25, 2017 at 11:06 AM
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
0