Compare columns, append missing from one column to the other

[Asher]

Hello,

I have 2 tables each with a "Person" column as the first column. Column 1 is a list of static names (and they must remain static so information from the rest of the table isn't lost) and column 2 is a list of names that updates from a macro.

I need to somehow compare the static column 1 to the updated column 2 and if any new names are in column 2 add them to the bottom of the static column 1.

I've tried vlookup and match but those just compare the data. I need to figure out how to append the static list.

Any ideas?

[HansV]

I wouldn't use formulas but a macro:

Code: Select all

Sub AppendList()
  ' Modify as needed
  Const lngCol1 = 1
  Const lngCol2 = 2
  Dim r As Long
  Dim m As Long
  Dim t As Long
  Dim rng As Range
  Application.ScreenUpdating = False
  t = Cells(Rows.Count, lngCol1).End(xlUp).Row
  Set rng = Range(Cells(1, lngCol1), Cells(t, lngCol1))
  m = Cells(Rows.Count, lngCol2).End(xlUp).Row
  For r = 1 To m
    If rng.Find(What:=Cells(r, lngCol2), LookAt:=xlWhole) Is Nothing Then
      t = t + 1
      Cells(t, lngCol1) = Cells(r, lngCol2)
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

[Asher]

Thanks Hans,

I have a question though. In order to make this work for me, am I supposed to switch the initialized value of the Constants lngCol1 and lngCol2 to the sheet and column range that the columns are?
Ex:

Code: Select all

Const lngCol1 = Worksheet("DataValidation").ListObjects("tblTDLevel").ListColumns(1)
  Const lngCol2 = Worksheet("StaffProjection").ListObjects("tblStaffProj").ListColumns(1)

Or is there another way it is supposed to know where to find the respective data?
Or does it just know somehow?

I'm crossing my fingers and hoping this isn't too dumb of a question.

[HansV]

I didn't (couldn't) know the exact situation, so to keep it easy I assumed that you would enter the correct values for the column numbers manually.

You could set the values in the code, but then you can't use constants, they have to be variables:

Code removed, made no sense
I'll post a new reply

[HansV]

I had also assumed that the two lists were on the same sheet. Since they aren't, the code has to be modified. You still have to enter the column numbers manually.

Code: Select all

Sub AppendList()
  ' Modify as needed
  Const lngCol1 = 23
  Const lngCol2 = 43
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim r As Long
  Dim m As Long
  Dim t As Long
  Dim rng As Range
  Set wsh1 = Worksheets("DataValidation")
  Set wsh2 = Worksheets("StaffProjection")
  Application.ScreenUpdating = False
  t = wsh1.Cells(wsh1.Rows.Count, lngCol1).End(xlUp).Row
  Set rng = wsh1.Range(wsh1.Cells(1, lngCol1), wsh1.Cells(t, lngCol1))
  m = wsh2.Cells(wsh2.Rows.Count, lngCol2).End(xlUp).Row
  For r = 1 To m
    If rng.Find(What:=wsh2.Cells(r, lngCol2), LookAt:=xlWhole) Is Nothing Then
      t = t + 1
      wsh1.Cells(t, lngCol1) = wsh2.Cells(r, lngCol2)
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

[Asher]

Is there any way to use this code with the Excel 2007 Table syntax tlike that in my pervious post? Because your code above,(works beautifully btw) takes even the stuff outside the table that are in that same column and I have a layout that has summary information in the first 11 rows above the table, so when I run the code, instead of just taking information from one table and putting it in the other table, it takes the entire column.

I'm attaching a stripped down copy of my file so you can see what I mean.

I mentioned in my first post that I was working with tables but I can see how I should have given more information. I keep trying to get a good balance between getting enough information in my posts and not being too wordy. I'll keep at it until I get it right.

[HansV]

I'll look at it later today; I'm currently on a PC with Excel 2003.

[HansV]

OK, here is a version that uses listobjects:

Code: Select all

Sub AppendList2()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim lob1 As ListObject
  Dim lob2 As ListObject
  Dim rng1 As Range
  Dim rng2 As Range
  Dim cel As Range

  Application.ScreenUpdating = False

  Set wsh1 = Worksheets("DataValidation")
  Set lob1 = wsh1.ListObjects("tblTDLevel")
  Set rng1 = lob1.DataBodyRange.Columns(1)

  Set wsh2 = Worksheets("StaffProjection")
  Set lob2 = wsh2.ListObjects("tblStaffProj")
  Set rng2 = lob2.DataBodyRange.Columns(1)

  For Each cel In rng1.Cells
    If rng2.Find(What:=cel.Value, LookAt:=xlWhole) Is Nothing Then
      lob2.ListRows.Add.Range.Cells(1).Value = cel.Value
    End If
  Next cel

  Application.ScreenUpdating = True
End Sub

[Asher]

Hans,

This is beautiful. Thanks so much.