Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Vba Copy And Compare Data In 2 Spreadsheets

  1. #1
    Join Date
    4th May 2007
    Posts
    4

    Vba Copy And Compare Data In 2 Spreadsheets

    Hello Everyone,

    To get to the point, I am trying to compare values in column "A" of my PRIMARY spreadsheet with values in column "A" of my SECONDARY spreadsheet. If match in PRIMARY is found in SECONDARY, copy column b and c from SENCONDARY and paste in b and c of PRIMARY. Next, test for next true statement until no other matches in PRIMARY.

    Any ideas of how to quickly and efficiently accomplish this task? Actually, I am now copying a spreadsheet from another workbook and pasting the content to another sheet which I am referring to as SECONDARY in the previous paragraph. Thanks for any help.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Hi,

    Does this help?

    VB:
    Sub MatchSheets() 
        Dim rng1 As Range 
        Dim rng2 As Range 
        Dim RowNo As Long 
         
        Set rng1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)) 
        Set rng2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp)) 
        For Each c In rng1 
            If Application.WorksheetFunction.CountIf(rng2, c) > 0 Then 
                RowNo = Application.WorksheetFunction.Match(c, rng2) 
                c.Offset(, 1).Resize(1, 2).Value = Worksheets("Sheet2").Range("B" & RowNo, "C" & RowNo).Value 
            End If 
        Next c 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    try
    VB:
    Sub test() 
        With Sheets("PRIMARY") 
            With .Range("a1",.Range("a" & rows.Count).End(xlUp)).Offset(,1).Resize(,2) 
                .Value = Array("=vlookup(a1,SECONDARY!a:c,2,false)","=vlookup(a1,SECONDARY!a:c,3,false)") 
                On Error Resume Next 
                .SpecialCells(-4123,16).[B]ClearContents[/B] 
                .Value = .Value 
            End With 
        End With 
    End Sub 
    
    
    Thanks to Reafidy
    Last edited by jindon; May 4th, 2007 at 10:19.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Quote Originally Posted by jindon
    try
    VB:
    Sub test() 
        With Sheets("PRIMARY") 
            With .Range("a1",.Range("a" & rows.Count).End(xlUp)).Offset(,1).Resize(,2) 
                .Value = Array("=vlookup(a1,SECONDARY!a:c,2,false)","=vlookup(a1,SECONDARY!a:c,3,false)") 
                On Error Resume Next 
                .SpecialCells(-4123,16).CelarContents 
                .Value = .Value 
            End With 
        End With 
    End Sub 
    
    
    typo: ClearContents

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th May 2007
    Posts
    4

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Reafidy,

    Thanks for the help on this...it works nearly perfect. What if I wanted to perform a check on range "b" to validate the processing? In other words, If cell in range b is blank, go to next item in range a. If not, process by comparing range a with range b.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    This was a whie ago - refresh my memory as to which is range B?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th May 2007
    Posts
    4

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    I am trying to compare values in column "A" of my PRIMARY spreadsheet with values in column "A" of my SECONDARY spreadsheet. If match in PRIMARY is found in SECONDARY, proram will check column "B" of PRIMARY for data. If column "B" is blank, copy column b and c from SENCONDARY and paste in b and c of PRIMARY. Next, test for next true statement until no other matches in PRIMARY. So, I am testing if "A" and "B" are true before copying any data.

    By the way, column "A" is numeric and column "B" is alpha

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Hi,

    Thanks for the refresher. This code should only copy if column A and B on the primary is not blank for the given row.

    VB:
    Sub MatchSheets() 
        Dim rng1 As Range 
        Dim rng2 As Range 
        Dim RowNo As Long 
         
        Set rng1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)) 
        Set rng2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp)) 
        For Each c In rng1 
            If Not c.Value = "" And Application.WorksheetFunction.CountIf(rng2, c) > 0 Then 
                RowNo = Application.WorksheetFunction.Match(c, rng2) 
                If c.Offset(, 1).Value = "" Then c.Offset(, 1).Resize(1, 2).Value _ 
                = Worksheets("Sheet2").Range("B" & RowNo, "C" & RowNo).Value 
            End If 
        Next c 
         
    End Sub 
    
    
    HTH

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    4th May 2007
    Posts
    4

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Perfect!!!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,896

    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Excellent, I probably should have put those conditions in at the start, never mind glad its working.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Compare Two Spreadsheets & Output Report
    By Whorf23 in forum EXCEL HELP
    Replies: 2
    Last Post: August 2nd, 2010, 03:07
  2. Compare Spreadsheets Against Master
    By project5k in forum EXCEL HELP
    Replies: 3
    Last Post: August 1st, 2007, 08:18
  3. Copy Data From Multiple Closed Spreadsheets
    By EthanSun in forum EXCEL HELP
    Replies: 5
    Last Post: June 25th, 2007, 15:55
  4. Compare Two Spreadsheets And Return Differences
    By Monica in forum EXCEL HELP
    Replies: 9
    Last Post: March 17th, 2007, 12:37
  5. Compare Data In Two Spreadsheets
    By trigwhu in forum EXCEL HELP
    Replies: 1
    Last Post: December 30th, 2006, 01:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno