Copying Unmatched fields from source to target worksheet (Excel 2010)

I have a workbook that contains two worksheets. 'patrickab' of experts exchange kindly wrote me some sample code that highlights any records in source that yet to appear in target (see attached txt file).

I would like to take this one stage further. Is it possible that if a part number in 'column b of source' is not present anywhere in 'column b of target', it could grab the whole relevant row and copy it over to the target worksheet. If so, how do I do this?

Thanks

Prior related question: http://www.experts-exchange.com/Q_26400577.html
NorthstarITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorthstarITAuthor Commented:
Oops, forgot to attach sample code
unmatched-fields-vba.txt
0
AndrewSwingewoodCommented:
Hi,
I have updated your sample code to copy the missing rows to the Target sheet.  Please let me know if this is what you were looking for.  If you require additional tweaks/features then I am happy to add them.

Sub missing_partnos()
Dim found As Boolean
found = False

Sheets("Target").Select
lastrowTarget = [b65536].End(xlUp).Row
Sheets("Source").Select
lastrowSource = [b65536].End(xlUp).Row

For i = 1 To lastrowSource
    Sheets("Target").Select
    lastrowTarget = [b65536].End(xlUp).Row
    For x = 1 To lastrowTarget
        If Sheets("Source").Cells(i, 2) = Sheets("Target").Cells(x, 2) Then
            found = True
        End If
    Next x
    If found = False Then
        Sheets("Source").Select
        Sheets("Source").Cells(i, 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Target").Select
        lastrowTarget = [b65536].End(xlUp).Row
        Sheets("Target").Cells(lastrowTarget, 1).Select
        Selection.Insert Shift:=xlDown
    End If
found = False
Next i
found = False
End Sub
CopyMissingRecords.xls
0
NorthstarITAuthor Commented:
Thank you for this andrew. Very much appreciated. Does the code you have kindly written for me compare the value(part no) in Source WS reference B2 with Target WS reference B2 to see if it exists and then copying the row over if it doesn't?

Thanks
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

AndrewSwingewoodCommented:
The code currently checks every value in column B of the source sheet against all values in column B of the Target sheet and if the value does not exist in the target, copies the entire row to a new row in the target sheet.  This means that the source and target sheets can be in different orders and the code will still work.

You can add a few values into column B of the source and target sheets in my example and prove this.  Is this what you were looking for?

Kind regards,
Andrew
0
NorthstarITAuthor Commented:
That soubnds fine. I will run it up and have a go shortly. Will obviously let you know the result.

Thanks Again
0
NorthstarITAuthor Commented:
In my live version of this file the sheets are now called Price-List and Data-Feed.

Obviously I have to change the Sheets("Target") etc to reflect the new names for target and source. DO I also have to change the 'lastrowTarget' statements to reflect the new names? I tried to do the last row ones and it insists on putting a space between Price - List e.g.

lastrowPrice - List = [b65536].End(xlUp).Row

Does that make sense? Also a question I would be asking you later on, seen as you offered to add bits, would it be possible to add a msgbox that says something like 'Update Complete - 27 new lines added' etc?

Thanks Andrew
0
AndrewSwingewoodCommented:
Hi,

you don't have to update the last row references, these are just variable names.  You only need to update the code where is says sheets("") to reflect the correct names of your sheets.  I have added a little code to the end of the module to provide you with the msgbox.

Sub missing_partnos()
Dim found As Boolean
Dim recordCount As Integer

found = False

Sheets("Target").Select
lastrowTarget = [b65536].End(xlUp).Row
Sheets("Source").Select
lastrowSource = [b65536].End(xlUp).Row
recordCount = 0

For i = 1 To lastrowSource
    Sheets("Target").Select
    lastrowTarget = [b65536].End(xlUp).Row
    For x = 1 To lastrowTarget
        If Sheets("Source").Cells(i, 2) = Sheets("Target").Cells(x, 2) Then
            found = True
        End If
    Next x
    If found = False Then
        Sheets("Source").Select
        Sheets("Source").Cells(i, 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Target").Select
        lastrowTarget = [b65536].End(xlUp).Row
        Sheets("Target").Cells(lastrowTarget, 1).Select
        Selection.Insert Shift:=xlDown
        recordCount = recordCount + 1
    End If
found = False
Next i
found = False
MsgBox ("Update Complete - " & recordCount & " records added")
End Sub

Kind regards,
Andrew
0
NorthstarITAuthor Commented:
Thank you, will give it a go and see how she runs. I appreciate your concerted efforts to help me with this

Thanks
0
NorthstarITAuthor Commented:
So far it s seems to run great. Thank you.

Question, is there someway that some form of visual clue could be displayed as it goes along to show that it is still calculating, like ' Row 1005 processed, Row 1006 Processed or something like that. I am concerned that the end user of this spreadsheet will be impatient so some form of counter might just calm him down a little

Just a thought. If not, then I will close this thread and award the points now

0
AndrewSwingewoodCommented:
Hi, I have added an update to the code so that the status bar at the bottom of Excel shows the row currently being processed.


Sub missing_partnos()
Dim found As Boolean
Dim recordCount As Integer

found = False

Sheets("Target").Select
lastrowTarget = [b65536].End(xlUp).Row
Sheets("Source").Select
lastrowSource = [b65536].End(xlUp).Row
recordCount = 0

For i = 1 To lastrowSource
    Sheets("Target").Select
    lastrowTarget = [b65536].End(xlUp).Row
    For x = 1 To lastrowTarget
        If Sheets("Source").Cells(i, 2) = Sheets("Target").Cells(x, 2) Then
            found = True
        End If
    Next x
    If found = False Then
        Sheets("Source").Select
        Sheets("Source").Cells(i, 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Target").Select
        lastrowTarget = [b65536].End(xlUp).Row
        Sheets("Target").Cells(lastrowTarget, 1).Select
        Selection.Insert Shift:=xlDown
        recordCount = recordCount + 1
    End If
found = False
Application.StatusBar = "Row " & i & " of " & lastrowSource & " processed"
Next i
found = False
MsgBox ("Update Complete - " & recordCount & " records added")
End Sub

Kind regards,
Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorthstarITAuthor Commented:
Hi Andrew

Here is the code I am using now thanks to you. I intend to now close this thread and award you the points. I am sure I will have more questions but I will reopen as you have more than answered my questions on here

Sub UpdateFormFeed()
Dim found As Boolean
Dim recordCount As Integer

found = False

Sheets("Master-Price-List").Select
lastrowTarget = [b65536].End(xlUp).Row
Sheets("Midwich-Feed").Select
lastrowSource = [b65536].End(xlUp).Row
recordCount = 0

For i = 1 To lastrowSource
    Sheets("Master-Price-List").Select
    lastrowTarget = [b65536].End(xlUp).Row
    For x = 1 To lastrowTarget
        If Sheets("Midwich-Feed").Cells(i, 2) = Sheets("Master-Price-List").Cells(x, 2) Then
            found = True
        End If
    Next x
    If found = False Then
        Sheets("Midwich-Feed").Select
        Sheets("Midwich-Feed").Cells(i, 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Master-Price-List").Select
        lastrowTarget = [b65536].End(xlUp).Row
        Sheets("Master-Price-List").Cells(lastrowTarget, 1).Select
        Selection.Insert Shift:=xlDown
        recordCount = recordCount + 1
    End If
found = False
Application.StatusBar = "Row " & i & " of " & lastrowSource & " processed"
Next i
found = False
MsgBox ("Update Complete - " & recordCount & " records added")
End Sub


Again my Thanks Andrew

Toni
0
NorthstarITAuthor Commented:
Flawless evolving code from Andrew. I have nothing but respect for you and your knowledge. Sincerest thanks

Toni
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.