Announcement

Collapse
No announcement yet.

Vba Copy And Compare Data In 2 Spreadsheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Vba Copy And Compare Data In 2 Spreadsheets

    Hi,

    Does this help?

    Code:
    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
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Vba Copy And Compare Data In 2 Spreadsheets

      try
      Code:
      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).ClearContents
                .Value = .Value
           End With
      End With
      End Sub
      Thanks to Reafidy
      Last edited by jindon; May 4th, 2007, 10:19.

      Comment


      • #4
        Re: Vba Copy And Compare Data In 2 Spreadsheets

        Originally posted by jindon
        try
        Code:
        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
        Reafidy

        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Vba Copy And Compare Data In 2 Spreadsheets

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

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment


            • #7
              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

              Comment


              • #8
                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.

                Code:
                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
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment


                • #9
                  Re: Vba Copy And Compare Data In 2 Spreadsheets

                  Perfect!!!

                  Comment


                  • #10


                    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.
                    Reafidy

                    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                    Comment

                    Working...
                    X