import data from 1 workbook to another and APPEND the data with out duplicates.

OK Gurus, I have another problem for you. I need a sub routine that will allow for a customer file to be opened from a command button (at the moment, I have it set up with the Application.GetOpenFilename method with now command button , just using the run command). If this is the most efficient way, than I will use that. After the file is open, I need to get the sheet name(S) from that file. (data should most always be on the first sheet. Fields and data will look like the example in book2). After getting the sheet names, I then need to copy the whole sheet to sheet TWO of the example "ImportTest.xlsm" . After getting it to sheet 2, the formatting takes off but I've got that working so unless you see a better way to do it, that doesn't need any help. Then I need to compare sheet2 with existing data on sheet1 to be sure there is no duplicates, delete any duplicates, copy THAT data and append it AT THE BOTTOM of the data on sheet one that will already be there. whew!! i think i explained it right. I'll start there and see what kind of replies I get. as always, Any questions, feel free to ask, ill do my best to answer them.

Thanks for your great work!!

ok.... here we go,

first- the file name will most likely never be the same name or be in the same folder nor will the sheet names be the same. The data will be coming from the same place but it is never built or sent out by the same person every time so the chances of anything being the same are pretty slim. So to answer your question, yes, the file picker dialogue pop up is the best option that I can think of.

2- explaining in detail, the quote 'After the file is open, I need to get the sheet name(S) from that file" I guess I really don't NEED the sheet name but my thought was that the sheet name or location of the sheet in the workbook could change breaking the procedure because, as explained above, the chances of the file names or the sheet names being the same is slim to none. So once the file being imported is open, I wanted to first check to see how many sheets there are, is there data in ALL sheets, if there is more than 1, and is that data the same or different in each sheet , if the data is ONLY in one sheet, even though there might be a single or multiple sheets, then, where ever the data is, compare the data in THAT sheet to the current data in the other workbook (which, right now is named importtest.xlsm and could change) . If ALL the data is the same in both places ( which it could be) then just quit the sub. If the data has different entries, than select JUST the different entries and append them to the bottom of the sheet "Voter Names" in importtest.xlsm.

3rd- when I wrote this post, my idea was to copy all data , AFTER it was formatted with the TextToColumns call, into a second sheet on importtest.xlsm. I've since refined my thoughts and figured out, that would be an extra and unnecessary step in what is already turning out to be a larger procedure than originally anticipated. Comparing the data in the the 2 sheets would be the better option if possible.

Let me take a minute and explain to you what this "App" that I'm building is for and what it is anticipated to do.

This import procedure is one cog in a very much larger App. The idea here is to build an all in one solution for towns and cities under the jurisdiction of a larger county seat. each "Town" or "City" is responsible for its citizens on the voter roll. if that citizen decides that they want to vote by absentee ballot, there is a huge procedure that needs to be performed by the city or town officials all while complying with local ordinances. The app holds all the voter information. when people leave the area, come to the area, die, are born, become voter age and register, don't register and don't vote for a few years then decide they want to later.... these are all reasons that they could be removed or added to the data I'm trying to import. Those are the entries I'm looking for. The data is built by employees in the COUNTY government, and can be sent to the Towns or Cities at their request. their particular data will be specific to them and their township. This is why the button exists. Its on the settings page of the userforms that aren't included in this sample simply because the sample is getting too big to send so I have to break it apart and work on bits at a time. That button will be for them to update their "Database" of names. Because the data comes from the county seat, it most likely is going to change and that presents the problems I'm attempting to address here.

I hope this helps you see my vision. As always, im open to suggestions and always welcome changes that will make the code and the app run better and be more efficient. Any questions, feel free to ask.

Cheers!

ok... after reading it again, that is a little confusing. what i was thinking is to check the file that is sent to see if there are multiple sheets and if those multiple sheets ALL had data but lets leave that part out. ill train the users to prep the file before import. lets just assume that the import file will have a single sheet and that data WILL have duplicates. ill send new samples with data in each. if we could import the data from book2 to importtest, that is what ill need to accomplish. Ive loaded both files with data, some of the data is duplicated. (towards the bottom)

just the first and last names would be fine. that should be enough definition to determine if it is a duplicate.

ok.... couple things. first, this is doing exactly what i wanted it to do in regards to importing. HOWEVER, the sheet(s) that sent you are missing some data that will be in the original import files that are used. i purposely left this data and columns off of the sheets I sent due to its sensitive nature. let me see if i can explain this a little better. The file that is selected, needs to have columns A-C deleted (the whole column(s)), Column G Deleted and columns I-N deleted. this should leave only columns A-C and column E on the sample I sent you. Boy that really sounds confusing when I read it. I hope that makes sense to you.

All of these removed columns in the original file as mentioned above, will have to be deleted before the compare is done. I didn't think about this until I was testing the import and it drug in ALL this redacted info. after the import is complete then i will be running a separate sub to clean and normalize the data and use text to columns to put it all into the correct format. which by the way, there WILL be headers (Column Names).

hopefully this makes sense.

I had to edit this line: "this should leave only columns A-C and column D on the sample I sent you." to read: "this should leave only columns A-C and column E on the sample I sent you"

I actually got that sorted on my own Mumps..... Thanks for all the help.

see the updated code below.

Sub CopyColumns()
Dim lastrow As Long, erow As Long, Rng As Range, ws As Worksheet, wb As Workbook
Dim filter As String, targetWorkbook As Workbook, Ret As Variant
Dim wsCopy As Worksheet, wsDest As Worksheet, CopyRow As Long, DestRow As Long

 Application.ScreenUpdating = False
 Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook
 Dim RngList As Object, desWS As Worksheet, srcWS As Worksheet, Val As String, arr1 As Variant, arr2 As Variant
 Set desWS = ThisWorkbook.Sheets("Voter Names")
 arr1 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
 Set flder = Application.FileDialog(msoFileDialogFilePicker)
 flder.Title = "Please Select a folder and file."
 FileChosen = flder.Show
 FileName = flder.SelectedItems(1)
 Set wkbSource = Workbooks.Open(FileName)
 Set srcWS = Sheets(1)
 
 
 With wkbSource.Sheets(1)
 .Range("I:N").Delete
 .Range("G:G").Delete
 .Range("A:C").Delete
 .Range("D:D").Select
  Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
 Range(Range("F1"), Range("F1").End(xlDown)).Select
Set Rng = Selection
For Each cell In Rng
cell.Value = Trim(cell)
Next cell
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False

 
 End With
 MsgBox "This could take a few minuets! Please be patient!"
 
 arr2 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
 Set RngList = CreateObject("Scripting.Dictionary")
 For i = LBound(arr1) To UBound(arr1, 1)
 Val = arr1(i, 1) & "|" & arr1(i, 2)
 If Not RngList.Exists(Val) Then
 RngList.Add Val, Nothing
 End If
 Next i
 For i = LBound(arr2) To UBound(arr2, 1)
 Val = arr2(i, 1) & "|" & arr2(i, 2)
 If Not RngList.Exists(Val) Then
 srcWS.Rows(i).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
 End If
 Next i
 wkbSource.Close False
 Application.ScreenUpdating = True
End Sub

After moving this code into the main app, I did find a couple issues I'll start with this one simply because by solving this, it may very well solve the rest of the issues. The main app uses a table with headers and the import sub you created, adds everything below the actual table. I'd like this to append the data into that table preserving the headers but adding dynamically to the table, either at the bottom of top. because this table is sorted, once refreshed, it should all be back in alphabetical order.

one other issue is, once the import completes, I need to concatenate columns B and C with a space between. This is a new thing for me so I have no idea how to even begin to form that part. Its easy to do as a formula but ive never used it in VBA format before.

I have also figured out the concatenate procedure so I'm good on that issue.

I think i got this all sorted. Ill let you know if i still need help.

Thanks!