Columns compare

[alexandros6600]

Hi all,
In the attached file i managed through a simple macro (button Compare) to check if the
values on Sheet 1 A COLUMN.(Master)
exist on Sheet 2 A COLUMN.
If there are missing values on Sheet 2 then these values should be added from
Sheet 1 (Master) A Col to
Sheet 2 A Col in the next available cell.

At first upon the execution if there is a missing value it is pasted in
A2 cell of Sheet 2 and not in A1.
I'd like it to be pasted in A1 or if someone could explain how to paste in whatever cell i want.

Second. Stepping up the process instead of simple columns, assuming there are tables that need to be compared how could it be possible to compare
table's 1 FIRST Column in Sheet 1 (Master) to
table's 2 FIRST Column in Sheet 2?
(The tables are of different dimensions and positions but we only interested on first Column)

Third Case sensitivity
If there is a value "ALPHA" and a value "alpha" in First Column of Table 1 Sheet 1 (Master) then only one time it is pasted in the corresponding Table 2 of Sheet 2. I would like it to be case sensitive.

Fourth Delete
If a value is deleted on Sheet 1 (Master) it should be deleted also on Sheet 2 and also maybe the Table's 2 Sheet 2 dimensions to be rearranged -shortened-.

 

[PCosta87]

Hi,

Please test the following and let me know if it works as intended:

Sub test()

    Dim arr(), brr() As String
    Dim c As Range
    Dim i As Integer
 
    ReDim arr(Range("Table2[Sheet 2 Column]").Rows.Count)
    For i = 0 To Range("Table2[Sheet 2 Column]").Rows.Count
        arr(i) = Range("Table2[Sheet 2 Column]").Cells(i + 1, 1)
    Next i
 
    ReDim brr(Range("Table1[Sheet1 Column]").Rows.Count)
    For i = 0 To Range("Table1[Sheet1 Column]").Rows.Count
        brr(i) = Range("Table1[Sheet1 Column]").Cells(i + 1, 1)
    Next i
         
    For Each c In Range("Table1[Sheet1 Column]")
        If IsInArray(c, arr()) = False Then
            c.Copy Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            ReDim Preserve arr(UBound(arr()) + 1)
            arr(UBound(arr())) = c
        End If
    Next c
 
    For Each c In Range("Table2[Sheet 2 Column]")
        If IsInArray(c, brr) = False Then
            c.EntireRow.Delete xlShiftUp
        End If
    Next c
 
End Sub

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean

    Dim element As Variant
    On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
            If element = valToBeFound Then
                IsInArray = True
                Exit Function
            End If
        Next element
    Exit Function
IsInArrayError:
    On Error GoTo 0
    IsInArray = False
End Function

On a side note, by default, VBA code is case sensitive... you have to specifically set the compare method if that is not what you want.

Hope this helps.

 

[alexandros6600]

First of all I'd like to thank you for your attention.

Unfortunately the code provided doesn't resolves any of the above difficulties.
Furthermore Table 2 doesn't expand.

Thanks again for your approach

 

[PCosta87]

Hi,

Unless I wrongly understood the requirement, it does all the above...
Just so I know we are on the same page, a few questions:

It should compare table1 with table2 and paste on table2 all unique missing contents from 1 (case sensitive)
Then, it should compare table2 with table1 a delete all contents that are not present on table1 from table2.

All this should be done expanding and shrinking table2.

Is there something I got wrong?
If not, did you test the sample file attached?... it was working correctly on my end.

Please advise

 

[alexandros6600]

What the...

My fault. Perhaps i opened an earlier version than the one you attached.
Everything works fine.
Sorry again.
Could you please provide me a book or some tutorials about these kind of vba programming?

Thanks again

 

[Marc L]

Hi !

Start here : Excel VBA …
​