[VBA] Put Table1 and Table2 together (override similarities from Table1) ?

[NessPJ]

Hello all,

I have a sheet with 3 tables:
B15:J4000 (source)
L15:T3511 (updates)
V15:AD7011 (new)

The first columns in all these tables contain a 'key' value.
I would like to transfer the contents of table B15:J4000 to the table V15:AD7011, but table L15:T3511 contains updates for only some of the values found in table B15:J4000. I would like to put all available contents from table L15:T3511 in table V15:AD7011 as well, but if a line with the same key already exists it should be overwritten by the line of data from table L15:T3511.

Is this possible?

 

[NessPJ]

You are very welcome.

I know its been a while, but i had one more question regarding this code and was hoping you or someone else could help me out.

I have testfile uploaded here:
https://www.dropbox.com/s/6llfa0kl7olw4wv/Testfile.xlsm?dl=0

I altered your code to use it on this sheet you see inside the file.

    Dim bottomG As Long
    bottomG = Columns("J").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    Dim bottomP As Long
    bottomP = Columns("X").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    Dim Compkey As Range
    Dim foundCompKey As Range
    Range("A2:J" & bottomG).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
    For Each Compkey In Range("X2:X" & bottomP)
        Set foundCompKey = Range("AJ2:AJ" & Cells(Rows.Count, "AJ").End(xlUp).Offset(1, 0).Row).Find(Compkey, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCompKey Is Nothing Then
            Range("O" & Compkey.Row & ":W" & Compkey.Row).Copy Cells(foundCompKey.Row, "AA")
            Range("AK" & Compkey.Row).Value = "X"
        Else
            Range("O" & Compkey.Row & ":W" & Compkey.Row).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
            Range("AK" & Compkey.Row).Value = "X"
        End If
    Next Compkey

What i would actually want is:
Table A:J is compared with Table O:X, but when a difference is found it will only copy the row from Table A:J onto position AA:AJ.
The values called "Compare key" are used to determine if the data is different or not.
At the moment the code will copy data from both tables into table AA:AJ.

I tried to understand what the code does and alter it to my own needs, but its not exactly doing what i want.
(The .Value = "X" was also added for testing purposes).

 

[mumps]

I don't quite follow what you want to do. This is what the macro currently does:

-It copies the range from A2 to the bottom of column J to the bottom of column AA
-It searches for each value in column X to see if it exists in column AJ
-If it finds that value, it copies the range O:W in that row to column AA in the same row
-If it doesn't find that value, it copies the range O:W in that row to the bottom of column AA

Could you please describe what you want to do in the same fashion as I have above?

 

[NessPJ]

I don't quite follow what you want to do. This is what the macro currently does:

-It copies the range from A2 to the bottom of column J to the bottom of column AA
-It searches for each value in column X to see if it exists in column AJ
-If it finds that value, it copies the range O:W in that row to column AA in the same row
-If it doesn't find that value, it copies the range O:W in that row to the bottom of column AA

Could you please describe what you want to do in the same fashion as I have above?

Hi!

Well what i would like to do exactly is:
- I would like to compare every Column J entry in table A:J to see if it exists in table O:X
- If it does not exist i would like the corresponding range from table A:J to be copied into the 3rd table located on range AA:AJ

A second step i would like to add is:
- If an entry for every Column O in table O:X exists in Column A from table A:J
- Copy the corresponding range from table O:X to a new table in Cell range AL:AU

 

[mumps]

I'm sorry but I still don't follow.

You want to check if every value in column J exists in column X.
If it does not exist in column X, you want to copy the corresponding range from A:J to the bottom of column AA.

You want to check if every value in column O exists in column A.
If it does exist in column A, you want to copy the corresponding range from O:X to the bottom of column AL.

Is this all correct? Do you want me to use the file you have already uploaded or would you like to upload a new fresh file?

I'm also confused by these lines of code in the macro you revised:

Dim bottomG As Long
bottom[COLOR="#FF0000"]G [/COLOR]= Columns("[COLOR="#FF0000"]J[/COLOR]").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Dim bottomP As Long
bottom[COLOR="#FF0000"]P [/COLOR]= Columns("[COLOR="#FF0000"]X[/COLOR]").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

You have bottomG as the last row in column J and bottomP as the last row in column X. Why is bottomG not the last row in column G and bottomP not the last row in column P? Please clarify.

 

[NessPJ]

I'm sorry but I still don't follow.
I'm also confused by these lines of code in the macro you revised:

Dim bottomG As Long
bottom[COLOR=#FF0000]G [/COLOR]= Columns("[COLOR=#FF0000]J[/COLOR]").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Dim bottomP As Long
bottom[COLOR=#FF0000]P [/COLOR]= Columns("[COLOR=#FF0000]X[/COLOR]").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

You have bottomG as the last row in column J and bottomP as the last row in column X. Why is bottomG not the last row in column G and bottomP not the last row in column P? Please clarify.

That's just a result of me messing around with VBA code i don't fully understand.

 

[NessPJ]

I'm sorry but I still don't follow.

You want to check if every value in column J exists in column X.
If it does not exist in column X, you want to copy the corresponding range from A:J to the bottom of column AA.

You want to check if every value in column O exists in column A.
If it does exist in column A, you want to copy the corresponding range from O:X to the bottom of column AL.

Is this all correct? Do you want me to use the file you have already uploaded or would you like to upload a new fresh file?

Okay, maybe my explanation was just vague. Let me try again :

- The code should check if every value in Column J exists in Column X
If it exists in Column X, it should copy the corresponding range from A:J to the first available line of Column AA.

After that:
- The code should check if every value in Column O exists in Column A
If it does not exist in Column A, it should copy the corresponding range from O:W to the first available line of Column AM.

Is this a better explanation?

 

[mumps]

Try:

Sub Import_AT_Data()
    Application.ScreenUpdating = False
    Dim Compkey As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Compkey In Range("X2", Range("X" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Compkey.Value) Then
            RngList.Add Compkey.Value, Nothing
        End If
    Next Compkey
    For Each Compkey In Range("J2", Range("J" & Rows.Count).End(xlUp))
        If RngList.Exists(Compkey.Value) Then
            Range("A" & Compkey.Row).Resize(, 10).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
        End If
    Next Compkey
    RngList.RemoveAll
    For Each Compkey In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Compkey.Value) Then
            RngList.Add Compkey.Value, Nothing
        End If
    Next Compkey
    For Each Compkey In Range("O2", Range("O" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Compkey.Value) Then
            Range("O" & Compkey.Row).Resize(, 9).Copy Cells(Rows.Count, "AM").End(xlUp).Offset(1, 0)
        End If
    Next Compkey
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[NessPJ]

Try:
[...]

Thanks so much for your help!

Just did a few tests and also decided to change the compare key (so the value becomes a bit more readible).
In both tests i noticed that the code seems to copy entries to table AA:AJ that do actually exists in both tables (A:J and O:X).

Could it be that the VBA is now copying the 'matches' instead of the 'differences' ?

The output in table AM:AU seems to work correctly (awesome)!

I have uploaded a new testfile here (without the sheet protection on as well).
https://www.dropbox.com/s/tdr39eljqi4fsdd/Testfile 4.xlsm?dl=0

 

[mumps]

In Post # 16, you said

The code should check if every value in Column J exists in Column X
If it exists in Column X, it should copy the corresponding range from A:J to the first available line of Column AA

The text in red means that it not only exists in column J but also exists in column X so it does copy the "matches". If you want to copy the differences, replace this part of code:

For Each Compkey In Range("J2", Range("J" & Rows.Count).End(xlUp))
        If RngList.Exists(Compkey.Value) Then
            Range("A" & Compkey.Row).Resize(, 10).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
        End If
    Next Compkey

with this:

For Each Compkey In Range("J2", Range("J" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Compkey.Value) Then
            Range("A" & Compkey.Row).Resize(, 10).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
        End If
    Next Compkey

 

[NessPJ]

This seems to do the trick exactly. Thanks so much for all your help!