Matching the values from one colum to another if two other columns match

Question

I have two lists, one has additional values to it in excel like this, Col2 is empty

Col1 Col2 Col3 Col4
xyz       xxx  111
xxx       xzy  312 
xzy       xyz  523
...       ...  ...

I want to check Col1 and Col3 for matches and copy values from Col4 to Col2 if Col1 and Col3 match into the matching rows on Col2 respectively or at least align those matches without messing up the relation between Col3 and Col.

so my endresult should look like this:

Col1 Col2 Col3 Col4
xyz  523  xxx  111
xxx  111  xzy  312 
xzy  312  xyz  523
...  ...  ...  ...

or we could just ignore Col2 and align it just like this

Col1 Col2 Col3 Col4
xyz       xyz  523
xxx       xxx  111 
xzy       xzy  312
...       ...  ...

I've tried several things such as VLOOKUP and such but failed miserably.

Solved! A similar problem was solved here

=vlookup(B1,$A$1:$BA$1577,2 false) I've tried around stuff like this for example but nothing worked for me, I'm not very knowledgeable about EXCEL — Leftorro
Try this =VLOOKUP(A:A,C:D,2,0) in cells in Col2 (I'm assuming Col1 is A etc) — chris neilsen
@AStidham =vlookup(B1,$A$1:$BA$1577,2 false) I've tried around stuff like this for example but nothing worked for me, I'm not very knowledgeable about EXCEL — Leftorro

TheEngineer TheEngineer · Accepted Answer · 2014-12-16T15:48:34

In case someone wants to use code for this, here are some short pieces of code to accomplish the two end result options that the OP was looking for:

OPTION 1 - Place matched value in Column 2

Sub Test()

Dim LastRow As Long
Dim i As Long, j As Long

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With ActiveSheet
    For i = 1 To LastRow
        For j = 1 To LastRow
            If .Cells(j, 3).Value = .Cells(i, 1).Value Then .Cells(i, 2).Value = .Cells(j, 4).Value
        Next j
    Next i
End With
Application.ScreenUpdating = True

End Sub

OPTION 2 - Rearrange Columns 3 & 4

Sub Test2()

Dim LastRow As Long
Dim i As Long, j As Long

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With ActiveSheet
    For i = 1 To LastRow
        For j = 1 To LastRow
            If .Cells(j, 3).Value = .Cells(i, 1).Value And j <> i Then
                .Range(.Cells(j, 3), .Cells(j, 4)).Cut
                .Range(.Cells(i, 3), .Cells(i, 4)).Insert
                Exit For
            End If
        Next j
    Next i
End With
Application.ScreenUpdating = True

End Sub

Note that if you delete Column 2, you'll need to change the references to Columns 3 & 4 for this one.

Matching the values from one colum to another if two other columns match

1 Answers