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

[tkoby11]

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

Code	Description
KS0001	A Los Vinateros Bravos, Pipeno Blanco 2020 1L
KS0002	Adrien 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

Code	Description
(I need KS0002 here)	Adrien Renoir, Le Terroir Extra Brut NV
KSxxxx	Adroît, Mourvedre 2019

 

[mumps]

Try:

Sub CopyCode()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, srcRng As Range
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    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

 

[tkoby11]

Hi, thanks for replying...I don't have experience with VBA. Is there no formulaic way to do this, vlookup, etc?

 

[mumps]

Place this formula in A2 of Sheet2 and copy down column A: =INDEX(Sheet1!A:B,MATCH(B3,Sheet1!B:B,0),1)

If you want to try the macro, do the following:
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro

There are easier ways to run macros such as clicking a button or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.

 

[tkoby11]

Thanks I will give this a shot, much appreciated!

 

[mumps]

You are very welcome.

 

[tkoby11]

Thanks again, I think we are close! This did not seem to work as intended, it copies a code from the sheet but not the one that matches the same items in column b.

Can I send you the spreadsheets to take a look? I tried to attache the image screenshot of them side by side but this forum won't allow me to upload a 1.7MB image!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not an image) of your two sheets.
Alternately, 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. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[tkoby11]

Thanks. I tried to install XL2BB but the install instructions are out of date or not the same for a my mac. Here is the One Drive link to the files:

Microsoft OneDrive

1drv.ms

 

[mumps]

I assumed that the two sheets were in the same workbook. Are the two sheets in separate workbooks such as the two workbooks you uploaded? If they are in two separate workbooks, in which workbook do you want to put the macro?