Solved

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

Posted on 2010-08-15
13
304 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now