Compare and separate data

[Closed]
Report
Posts
2
Registration date
Wednesday July 19, 2017
Status
Member
Last seen
July 20, 2017
-
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
-
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!

2 replies

Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
484
Hi J_Marley,

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

Best regards,
Trowa
Posts
2
Registration date
Wednesday July 19, 2017
Status
Member
Last seen
July 20, 2017

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.
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
484
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