+ Reply to Thread
Results 1 to 16 of 16

Compare Two Columns in Two different Workbook

  1. #1
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Post Compare Two Columns in Two different Workbook

    Hi

    Please help me to create to VBA Code. I want to compare two columns from Active Workbook (ex Workbook A) book to another workbook (Work Book B) of same two columns. and then the result of variance to be updated in output file.

    enclosed sample workbook fWorkbook A.PNGor your.

    Workbook B.PNG
    Attached Files Attached Files
    Last edited by SARAN89; 05-20-2020 at 04:36 AM.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    Hey SARAN89,

    Try the below code assuming you'll place it in the compare output workbook. You need to adjust the blue text based on your files

    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Hi Nankw83

    Thanks for your help. Code is working fine, however the file names are not fixed one, it will change every time when i run the macro.
    I will move both workbook A, and Workbook B to new sheet then i will run the VBA code. Can you please help me to make the changes.
    Attached Files Attached Files

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    I have changed the code to make the user select the files instead of hard-coding the file path in the VBA code

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Nanakw83 Thank you so much for your help. code is working fine. Is it possible to select the sheet also in the workbook (while browse to select files) instead hard coding active sheet. If possible please support

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    What about the range ? Is it always B5 & its current region or do you wish to select the range as well ?

  7. #7
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Compare Two Columns in Two different Workbook

    Hi Nankw83 better to include range as well. Please help

  8. #8
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Thanks Nankw83. I wish to select the range as well

  9. #9
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    How about now

    Please Login or Register  to view this content.
    Last edited by nankw83; 05-21-2020 at 08:00 AM. Reason: Adjusted code for redundant lines

  10. #10
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    HI Nankw83

    Getting runtime error on the below line

    Please Login or Register  to view this content.

  11. #11
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    Did you select a single cell as range ? If yes, then that doesn't make sense to develop the code for unless you need to code to select the current region of that cell for you. If not then could you tell me what happened exactly what is the full error message you are getting

  12. #12
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Hi Nankw83 sorry my mistake i was selected only the header rows.

    Now tested with range of Cells its working fine. Thank you so much

  13. #13
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    In case you need to click on any cell of the range instead of selecting manually selecting the current region, you can amend the below line

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Hi Nankw83

    Appreciating your support and help. Even if remove few line on browse file line. its working perfectly within worksheet. You are well understanding others requirements. once again thanks

  15. #15
    Forum Contributor
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    105

    Re: Compare Two Columns in Two different Workbook

    Yes if i change this line its working as described

    Set Rg = Application.InputBox("Select desired range", Type:=8).CurrentRegion

    will it possible on selection different columns? like A&C (Currently its A&B)

  16. #16
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Compare Two Columns in Two different Workbook

    No because once you select the range the data is then stored in an array & if you select non-contiguous data the code will not work the way it is expected.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-18-2019, 01:39 AM
  2. [SOLVED] VBA to compare first columns of 2 workbook sheets as selected from inputbox
    By Zimbo in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-03-2019, 07:21 AM
  3. [SOLVED] Loop through workbook and compare the columns
    By HXIO in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2018, 09:47 AM
  4. [SOLVED] Compare and copy two columns from each sheet in a workbook
    By sarajun_88 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-01-2018, 08:25 AM
  5. Replies: 1
    Last Post: 06-23-2011, 04:22 PM
  6. Compare two columns in one woorkbook, bounce results off of a different workbook.
    By goldenr1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2010, 12:19 AM
  7. Compare columns in 2 sheets in the same workbook and copy HELP NEEDED
    By ktab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2005, 08:23 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1