Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1
    Join Date
    22nd March 2013
    Posts
    4

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th March 2013
    Posts
    13

    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 at 22:54.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd March 2013
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd March 2013
    Posts
    4

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

    Anyone can help?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    20th March 2013
    Posts
    13

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th March 2013
    Posts
    127

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

    My effort - could do with cleaning up.

    VB:
    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

  7. #7
    Join Date
    25th March 2013
    Posts
    127

    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

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

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

    Try the atached
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th March 2013
    Posts
    127

    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

  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,483

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

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

    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. Replies: 8
    Last Post: January 25th, 2013, 20:03
  2. Replies: 4
    Last Post: February 12th, 2011, 00:38
  3. Replies: 3
    Last Post: January 14th, 2011, 02:24
  4. Compare 2 Names Ranges & Copy Difference
    By aggieman84 in forum EXCEL HELP
    Replies: 5
    Last Post: August 13th, 2006, 11:13
  5. Compare Columns, difference in third ...
    By Ronald2001 in forum EXCEL HELP
    Replies: 8
    Last Post: July 13th, 2006, 18:54

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