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

1. ## 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

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

I'd use slightly different approach. Test in Z5:
Formula:
`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).

3. ## 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. ## 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:
`Please Login or Register  to view this content.`

as it is now,
to
Formula:
`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 :-)

##### Users Browsing this Thread

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

#### 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