Comparing Multiple Criteria Across Two Worksheets to Find and Migrate Rows

[reberryjr]

I'm trying to find a way to identify instances from my Incoming Volume worksheet, that do not exist on my Working Population worksheet, and copy them over to the Working Population worksheet. I think I'm good on how to copy and paste, but I'm not certain on the best method to go through each row on the Incoming Volume worksheet, and determine if the record exists on the Working Population worksheet.

I would want to move record over if the value in column H and the value in column Q of the Incoming Volume worksheet does not match the value in column O and X of the Working Population worksheet.

Incoming Volume
Column H = AcctNbr
Column Q = Notified

Working Population
Column O = AcctNbr
Column X = Notified

 

[mumps]

The "x" is a variable that stores the row number where data will be pasted. It starts at row 2 and gets incremented by 1 each time it goes through the loop. So the first time through the loop data gets pasted at row 2. Then x = x + 1 increments the value of "x" to 3 for the second time through the loop and so on. I hope this makes sense.

 

[reberryjr]

That makes perfect sense! I encountered another bump that I'm going to mess around with some more, before I ask for help.

 

[reberryjr]

@mumps My understanding of the Rng.Value & Rng.Offset was that it basically concatenates those values into a single value that's added to the dictionary, then compared after the Next clause.

For Each Rng In rws2.Range("C2", rws2.Range("C" & rws2.Rows.Count).End(xlUp))
    If Not RngList.Exists(Rng.Value & Rng.Offset(0, 3) & Rng.Offset(0, 9)) Then
        RngList.Add Rng.Value & Rng.Offset(0, 3) & Rng.Offset(0, 9), Nothing
    End If
Next
For Each Rng In iws1.Range("A2", iws1.Range("A" & iws1.Rows.Count).End(xlUp))
    If Not RngList.Exists(Rng.Value & Rng.Offset(0, 2) & Rng.Offset(0, 4)) Then

In my test data, if I change the value in one of the columns in the offset criteria, it should be recognized as a new record (not in the dictionary), so it should add the record to the destination workbook. However, it's simply overwriting the existing value. If I create a completely new record, it is picked up as a new value and added to the destination workbook.

Perhaps my understanding of the Rng.Value & Rng.Offset is incorrect?

 

[mumps]

Your understanding is correct. The macro first adds the values from iws2 to the dictionary. Then it checks if the values from iws1 exist in the dictionary. The next time you run the macro, the dictionary starts off empty so any changed values in iws2 will simply be added. The previous values are gone so the macro doesn't know whether or not any value has been changed because it has nothing to compare it to. I think that you would need a Worksheet_Change macro to add the changed record to the destination workbook whenever a value is changed. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[reberryjr]

I tried responding earlier. I guess my response didn't go through.

I would think that if the concatenation takes the values of A+B+C = ABC, it would add a new record when it sees A+B+Q = ABQ; not overwrite ABC to ABQ.

Unfortunately, this is on my work laptop, and both Box and Dropbox are firewalled out. So, I'd have to recreate all of this on one of my personal laptops and upload it.

 

[mumps]

Sounds good. You wouldn't necessarily need all of the data, but just a representative amount so that a possible solution can be tested.

 

[reberryjr]

So, I went back and looked at your explanation of what X is, and thought maybe that's the wrinkle.

The "x" is a variable that stores the row number where data will be pasted. It starts at row 2

The destination workbook will cumulate data, so I wouldn't want the imported data to always map to row 2. Instead, it would go to the Last Row + 1. I tried using this code, and it didn't work. Not sure if that would help you help me or not.

x = rws2.Range("A" & Rows.Count).End(xlUp).Row

 

[mumps]

Here is the revised macro to take care of the starting row. This doesn't help with the other issue about recognizing a changed value as a new record. Were you able to upload the file?

Private Sub cmd_Import_Click()
    Application.ScreenUpdating = False
    Dim r, i As Workbook
    Dim rws2, iws1 As Worksheet
    Dim Rng As Range
    Dim RngList As Object
    Dim rLR2 As Long
    Dim x As Long
    Set r = ThisWorkbook
    Set rws2 = ThisWorkbook.Sheets("Consolidated")
    Set RngList = CreateObject("Scripting.Dictionary")
    Set i = Workbooks.Open("\\Path")
    Set iws1 = i.Sheets("Data")
    For Each Rng In rws2.Range("C2", rws2.Range("C" & rws2.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & Rng.Offset(0, 3) & Rng.Offset(0, 9)) Then
            RngList.Add Rng.Value & Rng.Offset(0, 3) & Rng.Offset(0, 9), Nothing
        End If
    Next Rng
    For Each Rng In iws1.Range("A2", iws1.Range("A" & iws1.Rows.Count).End(xlUp))
        x = rws2.Range("C" & Rows.Count).End(xlUp).Row + 1
        If Not RngList.Exists(Rng.Value & Rng.Offset(0, 2) & Rng.Offset(0, 4)) Then
            rws2.Range("C" & x) = iws1.Range("A" & Rng.Row)
            rws2.Range("D" & x) = iws1.Range("B" & Rng.Row)
            rws2.Range("F" & x) = iws1.Range("C" & Rng.Row)
            rws2.Range("J" & x) = iws1.Range("D" & Rng.Row)
            rws2.Range("L" & x) = iws1.Range("E" & Rng.Row)
            rws2.Range("M" & x) = iws1.Range("F" & Rng.Row)
            rws2.Range("N" & x) = iws1.Range("G" & Rng.Row)
            rws2.Range("O" & x) = iws1.Range("H" & Rng.Row)
            rws2.Range("P" & x) = iws1.Range("I" & Rng.Row)
            rws2.Range("Q" & x) = iws1.Range("J" & Rng.Row)
            rws2.Range("S" & x) = iws1.Range("K" & Rng.Row)
            rws2.Range("T" & x) = iws1.Range("L" & Rng.Row)
            rws2.Range("U" & x) = iws1.Range("M" & Rng.Row)
            rws2.Range("V" & x) = iws1.Range("N" & Rng.Row)
            rws2.Range("W" & x) = iws1.Range("O" & Rng.Row)
            rws2.Range("Y" & x) = iws1.Range("P" & Rng.Row)
            If iws1.Range("Q" & Rng.Row) = "" Then
                rws2.Range("Z" & x) = iws1.Range("R" & Rng.Row)
            Else
                rws2.Range("Z" & x) = Trim(iws1.Range("Q" & Rng.Row) & " " & iws1.Range("R" & Rng.Row))
            End If
            rws2.Range("AA" & x) = iws1.Range("S" & Rng.Row)
            rws2.Range("AB" & x) = iws1.Range("T" & Rng.Row)
            rws2.Range("AC" & x) = iws1.Range("U" & Rng.Row)
            rws2.Range("AD" & x) = iws1.Range("V" & Rng.Row)
            rws2.Range("AE" & x) = iws1.Range("W" & Rng.Row)
        End If
    Next Rng
    RngList.RemoveAll
    Workbooks("Pending_Import").Close
    Application.ScreenUpdating = True
End Sub

 

[reberryjr]

@mumps, actually...this solves both issues. Because one of the offset values changed, I want a new record to be created, which this does. So....THANK YOU!

 

[mumps]

Glad it all worked out.