+ Reply to Thread
Results 1 to 6 of 6

Compare with Range

  1. #1
    Registered User
    Join Date
    11-14-2011
    Location
    Dusseldorf, Germany
    MS-Off Ver
    Excel 2003
    Posts
    97

    Compare with Range

    Hello,

    Need you expert help

    I want to compare a cell with a range and return TRUE if the value of the cell is in that range.
    The problem is that I'm using huge tables... Excel limit... and using formulas is really really slow.

    so, for example, I would have in Sheet 1 in A column
    Cell 1
    Cell 2
    Cell 3
    Cell 4
    .
    .
    .
    Cell X

    and I need to compare each of these sells with Sheet 2 range of cells
    Cell 1
    Cell 2
    Cell 3
    Cell 4
    .
    .
    .
    Cell X

    and return in Sheet 1 in Column B TRUE if Cell from sheet 1 is in Range of Cells in Sheet2.

    The cells can be numbers or text... I mean some times I compare numbers... sometimes text...

    Thank you in advance!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Compare with Range

    Please Login or Register  to view this content.
    Last edited by AB33; 12-19-2012 at 08:52 AM.

  3. #3
    Registered User
    Join Date
    11-14-2011
    Location
    Dusseldorf, Germany
    MS-Off Ver
    Excel 2003
    Posts
    97

    Re: Compare with Range

    Thank you for the quick reply...

    This works from what I see... but the problem is that it is still really slow ... took 10 min. to get to the middle of the file... comparing 200000 rows with 200000.

    any idea if it could be faster?

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Compare with Range

    Okay!
    I would come up with a different code.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Compare with Range

    Try this one and see if the speed is improved.
    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Compare with Range

    This is much faster code

    Please Login or Register  to view this content.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1