Copy table column to another table column if cells are identical

**auslegung** · 05-02-2013, 12:57 PM

I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.

I have search long hours for a solution, have tried to implement vlookup() and index(match()), but cannot figure either one of them. Any help would be greatly appreciated.

fyi, Table1 is almost 1500 rows long, while Table2 is not quite 80.

**AB33** · 05-02-2013, 05:08 PM

It can be done by a code. Are you familiar on how to use and run a code?

**auslegung** · 05-06-2013, 05:17 PM

I have a little bit of familiarity with coding, but almost no knowledge of VBA syntax. It needs to be

if ( K === AT ) {
AB:AD = AW:AY
}

or something to that effect. Can you translate that to VBA?

Originally Posted by AB33

It can be done by a code. Are you familiar on how to use and run a code?

**auslegung** · 06-04-2013, 01:00 PM

Here's the solution. Replace Table45 and Table134 with any two tables, and AN, K, AC, and AQ with the appropriate cells.

Sub CopyLoop()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRowContact As Long, lngRowMaster As Long, lngLoopContact As Long, lngLoopMaster As Long

'Set Variable To Count Number Of Rows In Table1
lngRowContact = Range("Table45").Rows.Count + 1
lngRowMaster = Range("Table134").Rows.Count + 1


'Loop Through Table, Comparing Value In K to Value In AN
'And if they are identical, Copy AQ:AS from the row AN was in to AC:AE In the row K was in
For lngLoopContact = 2 To lngRowContact
    For lngLoopMaster = 2 To lngRowMaster
        If Range("AN" & lngLoopContact).Value = Range("K" & lngLoopMaster).Value Then _
            Range("AQ" & lngLoopContact & ":AS" & lngLoopContact).Copy Range("AC" & lngLoopMaster)
    Next lngLoopMaster
Next lngLoopContact

End Sub