Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-15
13
Medium Priority
?
327 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 46

Expert Comment

by:aikimark
ID: 33468786
0
 
LVL 3

Accepted Solution

by:
AndrewSwingewood earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

604 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