Copy Data from one worksheet to the next - only add row if the unique value is NOT there - otherwise just update

0

 So I am trying to take a spreadsheet that is taken from another system - and update another spreadsheet if the Unique ID for each record is NOT already found in the new spreadsheets.  

For example Record ABC1 is already on the Destination worksheets

Record XYZ2 is not

In the source data both of the above record exist.  I want the macro to check to see if the ABC1 is on the destination worksheet, and if it is just update certain values.  If not, copy the entire row and add it to the destination worksheet.

I can copy and paste all values...just trying to figure out the next one.[Sub AddNewOrUpdate()

'Defining Worksheets & Other

    Dim Impsheet As Worksheet

    Dim Destsheet As Worksheet

    Dim Search As Variant

    Dim IDs As Range

    Dim dest_last_row As Integer

    Set Impsheet = Sheets("SFEXport")

    Set Destsheet = Sheets("Destination")

    'Get the last row of the import worksheet

    Import_last_row = Sheets("SFExport").Cells(Rows.Count, 1).End(xlUp).Row

    'Get last row for destination sheet

    dest_last_row = Destsheet.Cells(Rows.Count, 1).End(xlUp).Row

    Set IDs = Destsheet.Range("A2:A" & dest_last_row)

'Output

For Each Cell In IDs

    If Cell.Value = Search Then

    Debug.Print "Found"

        'Update the cells that might change

        'DestSheet.Cells(MatchedRow, 4) = ImpSheet.Cells(ImpMatchedRow 4)

        'DestSheet.Cells(MatchedRow, 5) = ImpSheet.Cells(ImpMatchedRow, 5)

        'DestSheet.Cells(MatchedRow, 6) = ImpSheet.Cells(ImpMatchedRow, 6)

        Delete Impsheet.Cells(FoundRow, 1).EntireRow.Delete

        Exit Sub

    Else

    'Copy Data for new Worksheet

    Impsheet.Cells(j, 1).EntireRow.Copy Destsheet.Cells(dest_last_row + 1, 1)

    'Delete the Copied data

    Impsheet.Cells(j, 1).EntireRow.Delete

End Sub]

Think I am following the logic now

Answer
Discuss

Discussion

Hi there :) It seems like you already have some code for it, so go ahead and include the code that you already made and maybe a sample file would help as well. Basically, if you already have some working code, it will be easier to help by seeing that.
don (rep: 1979) Mar 8, '21 at 1:54 pm
You may have your logic upside down. Apparently the task is to go through each row of the imported file and check if the unique ID already exists in the recipient worksheet. If it does, update existing values with the imported ones. If it doesn't, create a new entry.
To start on the task you need to know which columns in Source and Target worksheets have the unique identifier. Next, you need to know which values are to be updated, if found. Finally, in case it's not found, you need to know which columns are to be copied to which columns to create a new entry.
Everything I listed above that you need to know is also what anybody trying to help you would need to know. Unfortunately the info isn't included in your question. Therefore helping you is extremely difficult. DoA, as they say in ambulance speak. Try attaching copies of your two workbooks to your question. You can still do that in edit mode.
Variatus (rep: 4864) Mar 8, '21 at 10:21 pm
i redid the original post to help I think
delaneyjay Mar 10, '21 at 4:03 pm
Sorry but your revision isn't clear to me (the original question has gone and you don't say which bits you revised).

You say "Think I am following the logic now" but what do you mean? The code above is incomplete  and doesn't match Module 2 of your now-attached file. It seems to mix my Answer with other code (and you left out the [ CODE ] and [ /CODE ] parts out which create the "code box").

Is your intention to check SFExport (add and revise lines in Destination as needed) then clear SFExport?
John_Ru (rep: 2117) Mar 10, '21 at 4:30 pm
I've assumed the latter and editted my Answer to suit- see Revision 1
John_Ru (rep: 2117) Mar 10, '21 at 5:03 pm
Add to Discussion

Answers

0

Hi.

This code should work (within your marco). It assumes you deliver a String variable called Search (or change it in the code below) and that your IDs are in column A (starting at A2). You need to add your code at the points identified by comments below:

Dim IDs As Range, LastRow As Integer

'  assumes you deliver a String variable called Search 
With Worksheets("Destination")

    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    Set IDs = .Range("A2:A" & LastRow)

    For Each Cell In IDs
        If Cell.Value = Search Then
        Debug.Print "Found"
            'Your code to update row (= Cell.Row)
            Exit Sub
        End If
    Next Cell
' Your code to add new row (= LastRow+1)

End With

REVISION 1: I think I've worked out what you want to do. In the attached revision to your file, I've added this code in Module 1 REVISION 2: Corrected second IF statement (for flag=False) and moved Updated=False,  dest_last_row= and Set IDs= statements within the For/Next loop (so multiple "Not found" items are added to Destination, changes in bold italics below):

Sub AddNewOrUpdate()

'Defining Worksheets & Other
    Dim Impsheet As Worksheet
    Dim Destsheet As Worksheet
    Dim Search As Variant
    Dim IDs As Range, Updated As Boolean
    Dim dest_last_row As Integer, Import_last_row As Integer

    Set Impsheet = Sheets("SFEXport")
    Set Destsheet = Sheets("Destination")

    'Get the last row of the import worksheet
    Import_last_row = Sheets("SFExport").Cells(Rows.Count, 1).End(xlUp).Row
    'Get last row for destination sheet
    dest_last_row = Destsheet.Cells(Rows.Count, 1).End(xlUp).Row

    Set IDs = Destsheet.Range("A2:A" & dest_last_row)

    

'Loop through Impsheet
For J = Import_last_row To 2 Step -1

Search = Cells(J, 1).Value
Updated = False
dest_last_row = Destsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set IDs = Destsheet.Range("A2:A" & dest_last_row)

    'Output
    For Each Cell In IDs
        If Cell.Value = Search Then
            'Update the cells that might change
            Destsheet.Cells(Cell.Row, 4) = Impsheet.Cells(J, 4)
            Destsheet.Cells(Cell.Row, 5) = Impsheet.Cells(J, 5)
            Destsheet.Cells(Cell.Row, 6) = Impsheet.Cells(J, 6)

            Impsheet.Cells(J, 1).EntireRow.Delete

            Updated = True ' set the flag

        End If
    Next Cell

    If Updated = False Then 'Existing row not found

        'Copy Data for new Worksheet
        Impsheet.Cells(J, 1).EntireRow.Copy Destsheet.Cells(dest_last_row + 1, 1)
        'Delete the Copied data
        Impsheet.Cells(J, 1).EntireRow.Delet

    End If

Next J

End Sub
So I loop through SFExport (as you did in Module 2) but change my code to set a new variable "Updated" (in bold above) when a value is found (since you're running with several rows), If it isn't found (and revised in Destination) then the row is copied. Either way, the row in SFExport is deleted and the row counter J reduced.

You'll need to run Module 1 from the VB Editor.

Hope this is what you need.

Discuss

Discussion

Okay = I think I see where you are going with this - my question is how do I create the Search string and then reference the correct row to update.  

Here is the progress of the code - I can't figure out how to upload the workbook if I can I will but hopefully you can see where I am going ..         
delaneyjay Mar 10, '21 at 2:38 pm
Sorry will repost with a picture of code - as you can tell I am new
delaneyjay Mar 10, '21 at 2:40 pm
Okay - high maintenance here - how do I post the code so the formatting keeps?
delaneyjay Mar 10, '21 at 2:47 pm
To post your code, press the CODE text button and paste your code where it says Code_Goes_Here but leave the code tags and square brackets either side. You can make parts of pasted text bold or italic if needed. 
Better still, send us an Excel file by editing your original Question and use the Add Files... button below the text area. Alter the text so it's clear it's a revision and add some explanation.

As you found, you can't send screenshot or the like.
John_Ru (rep: 2117) Mar 10, '21 at 3:42 pm
I've asked you to expand your question since I may not be the person who provides the best solution to your problem (and so other users can see if your problem is similar to theirs) . 
John_Ru (rep: 2117) Mar 10, '21 at 3:45 pm
BTW did you notice in the code pasted in my Answer that I told you how to "reference the the correct row to update"? It was in the commented lines (extracted and in bold below):
'Your code to update row (= Cell.Row) 
...
' Your code to add new row (= LastRow+1)
John_Ru (rep: 2117) Mar 10, '21 at 3:56 pm
See Revision 1 to my Answer. It works for me (or does what I think you seem to be attempting)
John_Ru (rep: 2117) Mar 10, '21 at 5:03 pm
This was great for the update function if they exist - the only thing that doesn't seem to working correctly is that if the cell is NOT found - it isn't copying the row - Its updating row 2 in the destination's information.  For example.

OppID #123 is updated with new info from SFExport to Destination (previously there in row 2)

OppID#456 is NOT found on the Destination wk however, its data is updated to record in row 2 - it does delete after the update from the SFExport

OppID#789 is NOT Found on the desintation sheet - it runs into an error and doesn't delete or move any data.

I am sure it has something easy I am missing.  Its' close.  Let me know if you need a sample book updated.
delaneyjay Mar 11, '21 at 11:40 am
Thanks for the feedback. See Revision 2 to my Answer (and corrected file). I realised the second IF test was wrong sadly.
John_Ru (rep: 2117) Mar 11, '21 at 12:19 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login