+ Reply to Thread
Results 1 to 11 of 11

Macro To Compare Worksheets And List Differences With Exceptions

  1. 04-13-2010, 08:41 AM #1
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Macro To Compare Worksheets And List Differences With Exceptions

    Hi

    Can someone please help me out with this, I have attached a sample to try and explain what I mean.

    As you will see I have 2 work sheets within the workbook, Info & Table

    What I am trying to do is have a macro that will look at Columns A & B in the Table work sheet against Columns C & D in the Info work sheet and then list the differences in column E in the Info worksheet

    The problem is they won’t always run in the same order so the best way round it is to look at column A in the Table worksheet then match that number against column C in the Info worksheet then check the B column in Table against the D column in Info if they are different list the correct one the E column in Info.

    The correct one will always be the one that is listed in the Table worksheet.

    I have over 30,000 listings in the workbook so this can usually take a lot of time; any help would be greatly appreciated.

    I have done the first 2 in the sample to show you what I mean these are listed in the Info sheet,
    A9 & A19
    Attached Files Attached Files
    Last edited by JimmiOO; 04-14-2010 at 07:52 AM.
    Reply With QuoteRegister To Reply
  2. 04-13-2010, 09:06 AM #2
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro To Compare Worksheets And List Differences With Exeptions

    Hi,
    does the attached help?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 04-13-2010, 09:11 AM #3
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exeptions

    Yes thats exactly what i want it to do,it has listed all the differences, but i dont see any macro on the sheets? as i said this is just a sample i have over 30,000 listings in both worksheets and can go upto 50,000.
    Reply With QuoteRegister To Reply
  4. 04-13-2010, 09:13 AM #4
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro To Compare Worksheets And List Differences With Exeptions

    Do you mean 30000 + rows ?
    Reply With QuoteRegister To Reply
  5. 04-13-2010, 09:21 AM #5
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exeptions

    Yes but it could be double that at times
    Reply With QuoteRegister To Reply
  6. 04-14-2010, 02:53 AM #6
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exeptions

    Can anyone give a little help with this please? or point me in the right direction if possible.
    Reply With QuoteRegister To Reply
  7. 04-14-2010, 03:09 AM #7
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro To Compare Worksheets And List Differences With Exceptions

    If the length of the Lookup table is variable, instead of hardcoding the lookup table(in our example Table!$A$2:$B$99), use Dynamic Ranges.
    To easily pull a formula down, insert in the first cell, and double click the handle at the right bottom corner of the selected cell ( which contains the formula)
    Reply With QuoteRegister To Reply
  8. 04-14-2010, 03:50 AM #8
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exceptions

    Hi Arthurbr thanks for that but i was looking for a macro if possible as its part of another sheet with a few different processes on it, all will have their own button.
    Reply With QuoteRegister To Reply
  9. 04-14-2010, 04:56 AM #9
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exceptions

    Ah sorry Arthurbr think i had a mental block lol, thanks for the great help sussed it now. Just one last thing, if i wanted to stop it showing #N/A, what would i need to change in the formula?
    Last edited by JimmiOO; 04-14-2010 at 04:59 AM.
    Reply With QuoteRegister To Reply
  10. 04-14-2010, 07:14 AM #10
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro To Compare Worksheets And List Differences With Exceptions

    Something like =if(isna(your_formula,"",your_formula))

    BTW never mind the mental block, happens everyday to me
    Reply With QuoteRegister To Reply
  11. 04-14-2010, 07:52 AM #11
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Compare Worksheets And List Differences With Exceptions

    Thanks very much for that, works great. Problem Solved.
    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)

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