+ Reply to Thread
Results 1 to 5 of 5

Excel VBA to compare rows from different worksheets

  1. 05-17-2018, 11:22 AM #1
    mainemojo
    mainemojo is offline
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Excel VBA to compare rows from different worksheets

    I have two worksheets (sometimes, they are in the same workbook, other times, they are from different workbook.

    The worksheets have many rows. What I would like to do is be able to compare all rows in both worksheets against each other. The following are examples. The number of rows should be based on used range and not manually defined. Also, the actual number of used columns may be greater than 3, so it shouldn't be confined to A:C.

    Worksheet1
    Please Login or Register  to view this content.
    Worksheet2
    Please Login or Register  to view this content.


    I would like the script to create a new worksheet and then put the results similar to the following. It should show items that only exist on one of the worksheets, list the worksheet it is on, and then the row contents.

    Worksheet3
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 05-17-2018, 11:48 AM #2
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Excel VBA to compare rows from different worksheets

    Quote Originally Posted by mainemojo View Post
    I have two worksheets
    As we have no worksheet to test …
    Reply With QuoteRegister To Reply
  3. 05-17-2018, 12:00 PM #3
    mainemojo
    mainemojo is offline
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Excel VBA to compare rows from different worksheets

    That's why I gave the examples above. The actual worksheets can't be sanitized for me to put up here. Sorry.
    Reply With QuoteRegister To Reply
  4. 05-17-2018, 12:24 PM #4
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Excel VBA to compare rows from different worksheets


    Can't waste time without any sample attachment neither a crystal clear technical explanation …

    See in VBA inner help the Dictionary object.
    Last edited by Marc L; 05-17-2018 at 12:26 PM.
    Reply With QuoteRegister To Reply
  5. 05-23-2018, 09:57 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Excel VBA to compare rows from different worksheets

    That's why I gave the examples above. The actual worksheets can't be sanitized for me to put up here.
    Making a spreadsheet out of the data from the examples would have helped, however thanks to the Text to Columns feature I made a couple of spreadsheets based on the data in post #1.
    This proposed solution employs a helper column each (which may be moved and/or hidden for aesthetic purposes) on sheets 1 and 2.
    As it appears that the names in column A determine which data matches and which doesn't, the helper columns for sheet 1 is populated using: =ISERROR(MATCH(A2,Sheet2!A$2:A$8,0))
    A similar formula is used on sheet 2.
    On sheet 3 the names are displayed using:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The worksheets are displayed using: =IF(B2="","",IF(ISNUMBER(MATCH(B2,Sheet1!A$2:A$8,0)),"Sheet1","Sheet2"))
    Columns C and to the right are populated using:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Code to Compare Two Worksheets with unequal no of Rows
    By mishrav98 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2013, 08:18 AM
  2. Macro To Find Rows With Duplicates, Compare Cells, And Delete Rows. - Excel
    By Kwame001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2013, 10:41 AM
  3. [SOLVED] Compare 2 worksheets by rows then columns
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2013, 06:44 PM
  4. Compare cells in two worksheets and copy the changed rows to a new sheet
    By vbvamsi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-01-2012, 05:25 PM
  5. compare two worksheets and marked the rows have differences
    By ccsmith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2011, 02:47 AM
  6. Compare rows across worksheets
    By gravelnrubber in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-23-2010, 03:13 AM
  7. Compare 2 worksheets & auto delete marked rows
    By peterlo in forum Excel - New Users/Basics
    Replies: 16
    Last Post: 08-01-2007, 06:02 AM
  8. compare two worksheets and delete rows
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-26-2006, 07:45 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1