Solved

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

Posted on 2010-08-15
13
315 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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