Copying rows from table to another excluding existing entries

[Lrees]

Hi everyone,
I'll try to explain as best as possible.
I have table of data that I export from a site on a daily basis that lists all incoming messages in a table (message reference, date & time, title etc.)
I want to record some additional information against each row on a separate master workbook I have saved. I've created the VBA to pull the table from the downloaded daily workbook and import into my master workbook on a separate worksheet ("Teambinder Dump") where I've also created the VBA code to get formatting to match.

1. "Teambinder Dump" worksheet has the imported data in Columns A-C with Row 1 being the headers
2. "Tracker" worksheet has my list that i'd like to continually add any new entries onto. Layout is the same as above however I also have additional information noted against each row in columns D-E.
3. All items in column A are unique as they relate to the message reference

Looking for some help on importing any new entries from worksheets "TeamBinder Dump" into "Tracker" (within the same worksheet) that don't already exist and add them to the end of the table, whilst also taking into account how both tables have additional rows over time.

The way I've currently done this is by arranging both tables based on column B (Date and time) in Old-New order and copying and pasting columns A-C from one to the other. I know this is a terrible solution as it runs a big risk of data getting misaligned.

Any help would be much appreciated.


Cheers!
Lewys

 

[jmacleary]

Hi there. One way would be to add a VLOOKUP formula to D2 of "Teambinder Dump" to lookup like this: =IF(ISNA(VLOOKUP(A2,Tracker!A:A,1,FALSE)),"New","Existing") and drag down. You can then filter on that column for "New" and just copy those across.

 

[Lrees]

Thanks!
That logic makes a little more sense than my currently risky way of doing it.
Ideally would like this to be done in VBA. Will trawl around and see if I can find pieces to make the code for filtering.

 

[Lrees]

Ok so I have trawled some VBA however i'm still not able to figure of find any similar scenario to manipulate.
I'm currently copying my data that I have been recording information against from the worksheet "tracker" to the bottom of the imported list titled on the worksheet "TeamBinder Dump".

Using the below code to totally remove duplicates then copying whats left to the bottom of my main list on "tracker" this was working to a certain extent however fails if my imported list does not include any line items I have in my list. Doesn't find a duplicate therefore copies that entry back.

In my head i'm thinking the correct way is to get the range of data n my imported list on worksheet "TeamBinder Dump" look to copy it to the base of my list on the worksheet "tracker" however dismiss if it's a duplicate value?

for context worksheet
"TeamBinder Dump" has the imported list in columns A-C that changes in length
"Tracker" is the list that all new items are imported into starting at the bottom of the table that also aligns across columns A-C.

Any help would is greatly appreciated

Sub TotallyRemoveDuplicates()
  Sheets("TeamBinder Dump").Activate
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  Range(Addr) = Evaluate("IF(COUNTIF(A:A," & Addr & ")>1,""#N/A""," & Addr & ")")
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[Lrees]

Managed to figure a way of doing (with info from another thread. VBA below for anyone looking at this thread in the future.

 Sub CopyUnique()Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheet2
Set sh2 = Sheet1
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A1:A" & lr)
For Each c In rng
If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2).Resize(1, 4) = c.Resize(1, 4).Value
End If
Next
End Sub