Announcement

Collapse
No announcement yet.

VBA code for compare two columns in excel, and copy the difference at the bottom

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

  • VBA code for compare two columns in excel, and copy the difference at the bottom



    Search from the web for a whiel but still can't find a solution, hope you guys can help.

    I want to write a VBA code in excel for below scenarios:
    There are A,B,C,D,E,F columns in a worksheet, A,B and C is from a pivot table, D,E and F is a copy for the pivot table. A,B,C will keep update from time to time as the data soruce from the pivot will change everytime when i update this excel worksheet.

    The worksheet first setup as below:
    A B C D E F
    1 ABC Company (123) New York Garment ABC Company (123) New York Garment
    2 DEF Company (456) LA Accessories DEF Company (456) LA Accessories
    3 GHI Company (789) Pittsburgh Accessories GHI Company (789) Pittsburgh Accessories
    4 JKL Company (891) Seattle Accessories JKL Company (891) Seattle Accessories
    5 MNO Company (912) Chicago Garment MNO Company (912) Chicago Garment
    Column D,E and F is just copy and paste from the pivot table in column A,B and C.
    So for now, i update the data source and the pivot table data is now change like below:
    A B C D E F
    1 ABC Company (123) New York Garment ABC Company (123) New York Garment
    2 123 Ltd(ABC) China Garment DEF Company (456) LA Accessories
    3 456 Ltd (ZHK) Peru Garment GHI Company (789) Pittsburgh Accessories
    4 JKL Company (891) Seattle Accessories JKL Company (891) Seattle Accessories
    5 789 Company (STU) Bangkok Garment MNO Company (912) Chicago Garment
    What i want to do is to have a VBA code, once the VBA code run, it will compare Column A and Column D. If any Data in Column A not exsit in Column D, it will then copy column A,B and C to the bottom of D,E and F evertytime when i run thsi VBA code, please see below expect outcome.
    A B C D E F
    1 ABC Company (123) New York Garment ABC Company (123) New York Garment
    2 123 Ltd(ABC) China Garment DEF Company (456) LA Accessories
    3 456 Ltd (ZHK) Peru Garment GHI Company (789) Pittsburgh Accessories
    4 JKL Company (891) Seattle Accessories JKL Company (891) Seattle Accessories
    5 789 Company (STU) Bangkok Garment MNO Company (912) Chicago Garment
    6 123 Ltd(ABC) China Garment
    7 456 Ltd (ZHK) Peru Garment
    8 789 Company (STU) Bangkok Garment
    Hope someone able to help, thanks!

  • #2
    Re: VBA code for compare two columns in excel, and copy the difference at the bottom

    which is the unique key for copy ? a+b+c coloumns ? or just a coloumn ?

    when you refresh pivot table (a/b/c coloumns) could it be same with added d/e/f before ?
    Last edited by doganbaris; March 22nd, 2013, 22:54.

    Comment


    • #3
      Re: VBA code for compare two columns in excel, and copy the difference at the bottom

      Once refresh the pivot table, a/b/c/ column and d/e/f/ column may not be the same, as the company detail in A/b/C column will keep changing and update. D/E/F column data will keep growing as i want a vba code to copy A/B/C column to D/E/F every time if A/B/C is not contain in D/E/F. The unique key for copy are A+B+C column.

      Comment


      • #4
        Re: VBA code for compare two columns in excel, and copy the difference at the bottom

        Anyone can help?

        Comment


        • #5
          Re: VBA code for compare two columns in excel, and copy the difference at the bottom

          A+B+C can be same again ?
          For Example,
          1 newly added A+B+C = "Firm" + "İstanbul" + "Accessories"
          2 we added these to D+E+F
          3 Refresh the Pivot table and Firm+İstanbul+Accessories deleted from pivot table ( But it is stil in the D+E+F )
          4 a few days ago , you refresh the pivot table, and A+B+C coloumns be "Firm" + "İstanbul" + "Accessories" again...
          5 if i agree the A+B+C unique , I will never add this again to D+E+F
          6 if you want to add this newly row , so , A+B+C is not unique.... Maybe you have to add another area to A+B+C+X , thus it can be really unique

          Comment


          • #6
            Re: VBA code for compare two columns in excel, and copy the difference at the bottom

            My effort - could do with cleaning up.

            Code:
            Sub Find_unique_Companies()
            'VBA Wizard
            '26 03 2013
            'VBA to compare tow lists in a table and return the unique companies
            'Pasting those uniques to the bottom of the second list.  The VBA code
            'then re-sets itself and checks again and again until all have ben found.
            'The code does not format the cells it is pasting to
            'I have not stress tested the code so post if find any problems
            'MAKE SURE YOU CHANGE THE RANGES TO MATCH WHERE THE TABLE IS POSITIONED ON YOUR WORKSHEET
            Start:
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Get size of new company list
            Dim new_company_arr() As String
            Dim new_company_count As Double
            new_company_count = WorksheetFunction.CountA(Range("B:B")) - 1 'CHECK RANGES COLUMN 2 LIST 1
            ReDim Preserve new_company_arr(3, new_company_count)
            'put new companies in array
            For i = 1 To new_company_count
                new_company_arr(1, i) = Range("B" & (i + 1)).Value 'CHECK RANGES COLUMN 2 LIST 1 '"B"
                new_company_arr(2, i) = Range("C" & (i + 1)).Value 'CHECK RANGES COLUMN 3 LIST 1 '"C"
                new_company_arr(3, i) = Range("D" & (i + 1)).Value 'CHECK RANGES COLUMN 4 LIST 1 '"D"
            Next i
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'get size of existing company list
            Dim existing_company_arr() As String
            Dim existing_company_count As Double
            existing_company_count = WorksheetFunction.CountA(Range("E:E")) - 1 'CHECK RANGES COLUMN 5 LIST 2
            ReDim Preserve existing_company_arr(3, existing_company_count)
            'put existing companies in array
            For j = 1 To existing_company_count
            existing_company_arr(1, j) = Range("E" & (j + 1)).Value 'CHECK RANGES COLUMN 5 LIST 2 "E"
            existing_company_arr(2, j) = Range("F" & (j + 1)).Value 'CHECK RANGES COLUMN 6 LIST 2 "F"
            existing_company_arr(3, j) = Range("G" & (j + 1)).Value 'CHECK RANGES COLUMN 7 LIST 2 "G"
            Next j
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Compares the 2 lists and finds the uniques
            For k = LBound(new_company_arr, 2) To UBound(new_company_arr, 2)
                For c = LBound(existing_company_arr, 2) To UBound(existing_company_arr, 2)
                  
                  If new_company_arr(1, k) = existing_company_arr(1, c) Then
                    Flag = 1
                    GoTo skip
                    Else
                  
                  End If
             
                Next
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'When it finds one it outputs to the sheet, no formatting
            'this part could be made nicer
                If Flag = 0 Then
                
                    Range("E1").Select 'CHAGE THIS ADDRESS TO TOP OF 2ND LIST [COLUMN TITLE]
                    Selection.End(xlDown).Select
                    ActiveCell.Offset(1, 0).Range("A1").Select
                
                    ActiveCell = new_company_arr(1, k)
                    ActiveCell.Offset(0, 1).Range("A1").Value = new_company_arr(2, k)
                    ActiveCell.Offset(0, 2).Range("A1").Value = new_company_arr(3, k)
                    
                    Flag = 0
                    GoTo Start
                Else
                End If
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                
            skip:
            Flag = 0
            Next
            End Sub
            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

            Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

            Thanks

            Comment


            • #7
              Re: VBA code for compare two columns in excel, and copy the difference at the bottom

              NOTE: I've been lazy in finding the last row so if starting from scratch make sure the first 2 table rows have something in them like this. You can delete the row of starts once the table is populated.
              A B C D E F
              ** ** ** ** ** ** **
              1
              2
              3
              4
              -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

              Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

              Thanks

              Comment


              • #8
                Re: VBA code for compare two columns in excel, and copy the difference at the bottom

                Try the atached
                Attached Files

                Comment


                • #9
                  Re: VBA code for compare two columns in excel, and copy the difference at the bottom

                  ignore
                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

                  Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

                  Thanks

                  Comment


                  • #10
                    Re: VBA code for compare two columns in excel, and copy the difference at the bottom

                    Originally posted by VBA_Wizard View Post
                    Jindon you hide your code, nothing dodgy in that code??
                    What are you talking about?

                    Comment


                    • #11
                      Re: VBA code for compare two columns in excel, and copy the difference at the bottom

                      Jindon - Thanks for the code couple of lovely tricks. Oh ignore that its the security settings here at work. Would not let me view the code as it was a temperory file. Started spewing out error messages and stuff looked like a great virus program initally. But soon worked out it was an issue at this end. Love the code, going to use aspects in subsequent programs, thanks.

                      Code:
                      x.Copy Cells(Rows.Count, 4).End(xlUp)(2)


                      and

                      Code:
                      Range("a1").CurrentRegion


                      and we won't talk about the fact i did not know this

                      Code:
                      With CreateObject("Scripting.Dictionary")
                                  .CompareMode = 1
                      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

                      Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

                      Thanks

                      Comment


                      • #12


                        Re: VBA code for compare two columns in excel, and copy the difference at the bottom

                        Cool, this is excatly what i need! Million thanks!

                        Comment

                        Working...
                        X