Solved

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

Posted on 2010-08-15
13
318 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:NorthstarIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
13 Comments
 

Author Comment

by:NorthstarIT
ID: 33440970
Oops, forgot to attach sample code
unmatched-fields-vba.txt
0
 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 33465048
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
 

Author Comment

by:NorthstarIT
ID: 33465980
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 33466028
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
 

Author Comment

by:NorthstarIT
ID: 33466098
That soubnds fine. I will run it up and have a go shortly. Will obviously let you know the result.

Thanks Again
0
 

Author Comment

by:NorthstarIT
ID: 33466452
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
 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 33466982
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
 

Author Comment

by:NorthstarIT
ID: 33467085
Thank you, will give it a go and see how she runs. I appreciate your concerted efforts to help me with this

Thanks
0
 

Author Comment

by:NorthstarIT
ID: 33468359
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
 
LVL 45

Expert Comment

by:aikimark
ID: 33468786
0
 
LVL 3

Accepted Solution

by:
AndrewSwingewood earned 500 total points
ID: 33469366
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
 

Author Comment

by:NorthstarIT
ID: 33469534
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
 

Author Closing Comment

by:NorthstarIT
ID: 33469539
Flawless evolving code from Andrew. I have nothing but respect for you and your knowledge. Sincerest thanks

Toni
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question