Compare and copy adjacent cell if cells match of the same field in 2 Separate Worksheets

tkoby11

New Member
Joined
Dec 18, 2021
Messages
23
Office Version
  1. 2021
Platform
  1. MacOS
Between 2 versions of the same spreadsheets (with some new data each new successive version), where column B cells or "Description" field are the same I want to copy the adjacent cell value (Column A or "Code") from Column A in sheet 1 to column A in sheet 2.

In worksheet 1, I have:

column A column B
CodeDescription
KS0001A Los Vinateros Bravos, Pipeno Blanco 2020 1L
KS0002Adrien Renoir, Le Terroir Extra Brut NV

In worksheet 2, I want to copy the text in the column "Code" for the same row where the Description is the same:

column A column B
CodeDescription
(I need KS0002 here)Adrien Renoir, Le Terroir Extra Brut NV
KSxxxxAdroît, Mourvedre 2019
 
Hi mumps, these are 2 different workbooks.

NC Inventory 12.15.21 FORMATTED (Old sheet)
NC Inventory 12.22.21 FORMATTED (new sheet trying to load and use the macro in)

Some background on the goal here. They are the same except that one is dated with inventory from the week before and the most recently dated one is the new inventory I need to load (already stale buy this Monday as I'll get a new one this week). The new sheet needs to have the same item number for the same items so the item numbers are consistent, but the quantity will change to the new inventory amounts. SOME items also drop off (I get rid of them with a different process) and some items will be new and I need to create a new custom item code.

The PROBLEM why I need this formula is because the supplier that sends me these stock sheets dumbfoundedly does not use inventory item codes so I have to make them up. I for too long just dealt with them changing. The problem is when a customer places an order from the old sheet data, if their orders fulfill after I add an updated new sheet, if the item codes are not the same (and they are not as the order is always different as the inventory changes each sheet), they will get an email with the wrong products being confirmed to them as being sent. That is why I need a look back to the old sheet to reference the same item code.

Make sense?
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I assume that the names of the two workbooks will change depending on the date. Because of this, I suggest you use the macro approach instead of the formulas because the macro eliminates the need to change the workbook names in the formulas every time the workbook names change. Start by making sure that both workbooks are open and that they are the only two that are open. Delete the formulas in both workbooks. Place the macro in a regular module in the new workbook and run it from there. The only issue that I can see is that you will have to copy/paste the macro into a module in each new workbook.
VBA Code:
Sub CopyCode()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, srcRng As Range, WB As Workbook, srcWB As Workbook, desWB As Workbook
    Set srcWB = ThisWorkbook
    For Each WB In Workbooks
        If WB.Name <> srcWB.Name Then
            Set desWB = Workbooks(WB.Name)
        End If
    Next WB
    Set srcWS = srcWB.Sheets("Sheet1")
    Set desWS = desWB.Sheets("Sheet1")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set srcRng = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp))
    For i = 1 To UBound(v)
        If Not IsError(Application.Match(v(i, 2), srcRng, 0)) Then
            x = Application.Match(v(i, 2), srcRng, 0)
            desWS.Range("A" & x + 1) = v(i, 1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
I assume that the names of the two workbooks will change depending on the date. Because of this, I suggest you use the macro approach instead of the formulas because the macro eliminates the need to change the workbook names in the formulas every time the workbook names change. Start by making sure that both workbooks are open and that they are the only two that are open. Delete the formulas in both workbooks. Place the macro in a regular module in the new workbook and run it from there. The only issue that I can see is that you will have to copy/paste the macro into a module in each new workbook.
VBA Code:
Sub CopyCode()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, srcRng As Range, WB As Workbook, srcWB As Workbook, desWB As Workbook
    Set srcWB = ThisWorkbook
    For Each WB In Workbooks
        If WB.Name <> srcWB.Name Then
            Set desWB = Workbooks(WB.Name)
        End If
    Next WB
    Set srcWS = srcWB.Sheets("Sheet1")
    Set desWS = desWB.Sheets("Sheet1")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set srcRng = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp))
    For i = 1 To UBound(v)
        If Not IsError(Application.Match(v(i, 2), srcRng, 0)) Then
            x = Application.Match(v(i, 2), srcRng, 0)
            desWS.Range("A" & x + 1) = v(i, 1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
Thanks again I'll give this a try too. Thanks for sticking with me on this one!
 
Upvote 0
Hi Mumps! I finally got out from under the holiday crush at work and ran this macro and IT WORKED. THANK YOU!

IS there an easy way to edit this macro to create a new module or edit this existing one to also copy in columns H, I & J?
 
Upvote 0
Could you upload copies of your workbooks and include a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets.
 
Upvote 0
Sure thing, thanks. Files are here:



File 1 - target file

NC 1.3.22 kellogg FORMATTED (target sheet, data in columns A, C, D, H, I, J where available should be taken from the below sheet where column B matches)

File 2 - source:

NC Inventory 11.22.21 FORMATTED with Macro

***file names will change as we move forward in time with the new dates of each file***
 
Upvote 0
Try:
VBA Code:
Sub CopyCode()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, srcRng As Range, WB As Workbook, srcWB As Workbook, desWB As Workbook
    Set srcWB = ThisWorkbook
    For Each WB In Workbooks
        If WB.Name <> srcWB.Name Then
            Set desWB = Workbooks(WB.Name)
        End If
    Next WB
    Set srcWS = srcWB.Sheets("Sheet1")
    Set desWS = desWB.Sheets("Sheet1")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Value
    Set srcRng = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp))
    For i = 1 To UBound(v)
        If Not IsError(Application.Match(v(i, 2), srcRng, 0)) Then
            x = Application.Match(v(i, 2), srcRng, 0)
            With desWS
                .Range("A" & x + 1) = v(i, 1)
                .Range("C" & x + 1).Resize(, 2).Value = Array(v(i, 3), v(i, 4))
                .Range("H" & x + 1).Resize(, 3).Value = Array(v(i, 8), v(i, 9), v(i, 10))
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi, this one did not seem to do anything. I have the 2 files saved in the following link to use. The 1.19 file needs to pull the data matching in column b from the 1.13 file

 
Upvote 0
They should not be, I checked the link and the files again by opening them...lmk
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top