Find missing values between two columns with VBA

[Eraaz]

Hello,


I need your help, i have 2 columns that are not on the same sheet, the first one is a table and the second one is updated at each opening of the excel file.
I would like to compare both columns, and if a data is in the column 2 and not in the column 1, add it at the end of the 1st column.

I can't copy the whole column because it is linked to a Sharepoint.

This is my code right now.

Sub LinkSheetToSharePoint()  
  SiteURL = "myURL"     'URL to site (without trailing slash)
  TargetSheetName = "Feuil1"                                            'Target Excel sheet name
  ViewGUID = "{B3C3255D-40A3-4583-8461-A54EA8237FFB}"                   'View GUID (can be obtained from the Edit View page URL)
  ListName = "Supplier"                                                'List to be linked


  Set TableList = ThisWorkbook.Sheets(TargetSheetName).ListObjects.Add(SourceType:=xlSrcExternal, _
  Source:=Array(SiteURL & "/_vti_bin", ListName, ViewGUID), _
  LinkSource:=True, _
  Destination:=ThisWorkbook.Sheets(TargetSheetName).Range("A1"))
  TableList.Name = ListName
  
End Sub


Sub RecoverData()
'Find "Name" in Row 1
Dim x As Workbook
 
    '## Open both workbooks first:
    Set x = Workbooks.Open(Application.ActiveWorkbook.Path & "\Suppliers ex Morpho.xlsx")
 
    With x.Sheets("Database").Rows(1)
        Set t = .Find("Vendor name", lookat:=xlPart)
        'If found, copy the column to Sheet 2, Column A
        'If not found, present a message
        If Not t Is Nothing Then
            Columns(t.Column).EntireColumn.Copy _
            Destination:=ThisWorkbook.Sheets("Feuil2").Range("A1")
        Else: MsgBox "Column Name Not Found"
        End If
    End With
    With x.Sheets("Database").Rows(1)
        Set b = .Find("Vendor account", lookat:=xlPart)
        'If found, copy the column to Sheet 2, Column A
        'If not found, present a message
        If Not b Is Nothing Then
            Columns(b.Column).EntireColumn.Copy _
            Destination:=ThisWorkbook.Sheets("Feuil2").Range("B1")
        Else: MsgBox "Column Name Not Found"
        End If
    End With
  x.Close
End Sub

I tryied to be as clear as possible, but if it is not, tell me i will try to do it another way.
Thank you.

 

[mumps]

The code you posted is not comparing any columns. It is simply copying the whole column from one sheet to another. What is the name of the sheet containing the first column and what is the column letter of that column and what is the name of the sheet containing the second column and what is the column letter of that column?

 

[Eraaz]

The code you posted is not comparing any columns. It is simply copying the whole column from one sheet to another. What is the name of the sheet containing the first column and what is the column letter of that column and what is the name of the sheet containing the second column and what is the column letter of that column?

I know it's not comparing any columns, i have no idea how to do it.
The name of the sheet for the first column is 'Feuil1' and the column is A
The second sheet is 'Feuil2' and the letter of the column is A

 

[mumps]

Try:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    With Sheets("Feuil1")
        For Each Rng In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
            If Not RngList.Exists(Rng.Value) Then
                RngList.Add Rng.Value, Nothing
            End If
        Next
    End With
    With Sheets("Feuil2")
        For Each Rng In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
            If Not RngList.Exists(Rng.Value) Then
                Sheets("Feuil1").Cells(Sheets("Feuil1").Rows.Count, "A").End(xlUp).Offset(1, 0) = Rng
            End If
        Next
    End With
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[Eraaz]

Thank you it work very well !

 

[mumps]

You are very welcome.

 

[OilEconomist]

Thank you it work very well !

@Eraaz, I also used it and worked for me. Can you mark @mumps 's solution (Response #5) as the correct solution.

 

[OilEconomist]

You are very welcome.

@mumps! Thanks so much for this even though I did not request it. Works well!

 

[mumps]

You are very welcome.

 

[OilEconomist]

You are very welcome.

I forgot to add I was looking for something exactly like this and used it!