compare 2 columns, in 2 worksheets, & if matched, return values from other columns

[craigexcel]

I had to copy a worksheet from my working file (workbook), as its own single-sheet workbook, so a coworker could work on that data independently. (Not my choice, but what was required. )
So now, that stand-alone worksheet has updated values in it, but the worksheet it was copied from does not ... and needs to be updated, so it can have the correct updated values.

Between the 2 worksheets, I want to find where Columns A & B are a match (by record / row), and then return, by formula, in the primary workbook, those values from the stand-alone worksheet for the other fields for each record (Columns C-W).

The data in the standalone worksheet has been reordered, and not sure how, but they're defintely not in the same order as when I sent the file originally.

Would greatly appreciate your suggestions for how to approach this one. Thanks!

edit: I have been searching for a couple of days on this, but I have only been able to find references to matching 1 column with another column, not matching 2 columns between 2 worksheets.

 

[mumps]

First of all, we would need to know the name of the single-sheet workbook including the extension (xls, xlsx, xlsm) and the name of the sheet in that workbook. We would also need to know the name of the worksheet in your working file workbook. Do the sheets have header rows with the data starting in row 2? For clarification, do you want to copy the data that has been added to the stand-alone sheet which is now not in your working sheet or copy the data "where Columns A & B are a match"? Please clarify in detail exactly what you want to copy.

 

[craigexcel]

Thanks for your reply mumps!
I'm using Office 365.
1. single-sheet workbook name: Cost Center Analysis.xlsx, and the sheet's name is "2928 Final Proposed CCs"
2. working file workbook name: Cost Center Analysis CHall.xlsx, and that sheet's name is the same as above
3. Both sheets have the same header row, where Column A = "Cost Center", and Column B = "Proposed Mapping Future CC", in Row 4.
4. As to which data to copy, if I read that part of your reply correctly, it's actually both. First to further clarify, I actually only need the data from Columns D & E to be retrieved. Some of those data were already correct before I sent my coworker the stand-along worksheet/workbook. Therefore, only some of the data in Columns D & E were updated afterward, and now need to be updated in the working file.
Column D header = "REGION", and Column E header = "COUNTRY". Where either Column D <OR> E in the working file has a value of "None", those are the values that should be replaced with the updated data from the stand-along worksheet, based on the Column A & B values being a match between the 2 sheets.

 

[mumps]

In some rows, either Column D or column E in the working file has a value of "None". Do the cells in column A and B in those rows contain the "Cost Center" and "Proposed Mapping Future CC", respectively and are those same values also in the single-sheet workbook in columns A and B? In other words, both workbooks contain the same data in columns A and B but the working file is missing some data in columns D and E. Is this correct?

 

[craigexcel]

Yes, mumps, that's exactly right. And further, after a quick check, the value "None" always appear together (same value in both Column D & E).

 

[mumps]

Make sure that both workbooks are open. Place this macro in a regular module in your working file workbook and save the workbook as a macro-enabled file. Run the macro.

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, foundVal As Range, sAddr As String
    Dim WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Workbooks("Cost Center Analysis.xlsx").Sheets("2928 Final Proposed CCs")
    Set WS2 = ThisWorkbook.Sheets("2928 Final Proposed CCs")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS1.Range("A5", WS1.Range("A" & WS1.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In WS2.Range("A5", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        If RngList.Exists(Rng.Value) Then
            Set foundVal = WS1.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                sAddr = foundVal.Address
                Do
                    If foundVal.Offset(0, 1) = Rng.Offset(0, 1) Then
                        Rng.Offset(0, 3).Resize(1, 2).Value = foundVal.Offset(0, 3).Resize(1, 2).Value
                    End If
                    Set foundVal = WS1.Range("A:A").FindNext(foundVal)
                Loop While foundVal.Address <> sAddr
            End If
            sAddr = ""
        End If
    Next Rng
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[craigexcel]

Thx very much mumps! That's way more than I was hoping for! I do appreciate your time in putting that together.

I'm running that code now, but it hasn't completed yet. Could you venture a guesstimate as to how long it might take this code to run for ~ 2,800 records? About 7 minutes in now, and still going. Not as familiar with some parts of your code, so harder for me to guesstimate.

 

[craigexcel]

I have been reviewing your code today to better understand what each line does. Can you explain what "Loop While foundVal.Address <> sAddr" is doing? In the Do loop, when / how do each of the variables change?

 

[mumps]

Here is the macro with explanatory comments:

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, foundVal As Range, sAddr As String
    Dim WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Workbooks("Cost Center Analysis.xlsx").Sheets("2928 Final Proposed CCs")
    Set WS2 = ThisWorkbook.Sheets("2928 Final Proposed CCs")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS1.Range("A5", WS1.Range("A" & WS1.Rows.Count).End(xlUp)) 'This loop stores each value in column A of WS1 in a dictionary
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In WS2.Range("A5", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        If RngList.Exists(Rng.Value) Then 'This loop checks each value in column A of WS2 to see if it is in the dictionary
            Set foundVal = WS1.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole) 'If it matches a value in the dicitonary, it searches for the first value in column A of WS1
            If Not foundVal Is Nothing Then
                sAddr = foundVal.Address 'When found, it sets sAddr to the value's address
                Do 'This loop searches column A of WS1 to see if there are any other instances of the same value
                    If foundVal.Offset(0, 1) = Rng.Offset(0, 1) Then 'Checks if the values in column B match
                        Rng.Offset(0, 3).Resize(1, 2).Value = foundVal.Offset(0, 3).Resize(1, 2).Value 'If they match, copies values in columns D and E
                    End If
                    Set foundVal = WS1.Range("A:A").FindNext(foundVal)
                Loop While foundVal.Address <> sAddr 'Keeps searching until it gets back to the first instance of the value
            End If
            sAddr = ""
        End If
    Next Rng
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

Did the macro finally work properly? Loops are generally not the fastest way to do things. However, I couldn't think of another approach. I wouldn't think it should take that long even with 2800 records. 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.

 

[craigexcel]

Thanks again, mumps, for the explanatory text. I haven't yet revisited this since this past Friday, but am hoping to try running it again today, to see if it will run to completion. Will consider uploading a scrubbed file if it looks like that might be necessary.