+ Reply to Thread
Results 1 to 4 of 4

Using Find or some other InStr like function with an if and formula.

  1. #1
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Using Find or some other InStr like function with an if and formula.

    Hi,

    My formula at the moment is working pretty good but I need to add one more thing...

    For Cell Z5 for example it should check A1 (by offsetting from the company cell?) to see if the cell contains "2 Nine" or "TOD" if it contains either of these then it should return false (blank)

    Obviously this needs to do it for each row, so in my example to the left column Z should look like column AA

    Current formula:
    Please Login or Register  to view this content.
    3T4gND9.png

    I would greatly appreciate the help sorting out this formula, it's annoying me, I've tried to get it to work using Find() but I can't do it
    Attached Files Attached Files
    Last edited by Hyflex; 03-02-2015 at 09:31 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Using Find or some other InStr like function with an if and formula.

    I'd use slightly different approach. Test in Z5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    it uses structure of your data:
    if column M is empty it gets empty. Number increase only in row just below COMPANY in A, and only when 4 rows above there is neither 2 Nine, nor TOD
    otherwise a cell from above is copied (but remember if M is empty it gets empty).
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Using Find or some other InStr like function with an if and formula.

    Hi Kaper,

    How can I check which way would be faster over bigger datasets?

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Using Find or some other InStr like function with an if and formula.

    This one shall be similar (in speed) to previous one, while new functionality (skipping two rooms) is added. If one would try to add checking last occurence of TOD or 2 Nine - to old one, the new one shall be much faster.

    If your dataset is really big (say 10000 rows+) one can consider change the formula after first few rows.
    Let's say that estimated max distance between two lines with COMPANY is X and let's assume it is 12 as in your sample data: 12 rows between rows 4 and 16.
    So in some 12*1.5 (added-security factor) = 18 row after formula was first introduced, so in row 23, we change the formula from
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    as it is now,
    to
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy this one down. Will be blistering-quick :-P

    Or to answer directly - how to check - just generate enough data and test each of them, comparying calculation time :-)
    Last edited by Kaper; 03-02-2015 at 11:19 AM.

+ 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. Use InStr function in formula?
    By Lee Hunter in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-09-2017, 01:12 PM
  2. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  3. [SOLVED] If Instr - find last row of data above and delete everything in between
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2013, 03:54 PM
  4. [SOLVED] How do I use InStr to find a '-' in the filename?
    By Lee-337 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2013, 02:32 PM
  5. [SOLVED] is there an equal fxn for 'InStr' in excel. Not Find or Search
    By Clausius in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM

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