Tricky filtering

[bibu]

Hello world!
In the attached file we have a list of tennis matches from a specific player "John Isner"
What I would like to do is filter the data so that only the matches where his odds (the H or I column) are over 1.6.
It would be very easy with the auto-filter, but the problem is that his odds are not always in the same column. When the match is won that is, when the L column is "positive" (2-0, 2-1, 3-0 etc), his odds are in the H column. When the L column is "negative", his odds are in the I column. Is there any way to filter this so the only visible cells will be where the "Q" column is "selected" and his odds are over 1.6, regardless of winning or losing the match?

Thanks for your time!

[Richard Buttrey]

Hi,

Why not add the helper column P and populate it with

=(OR(H2>1.6,I2>1.6),"Selected","")

Now autofilter using columns P&Q for the word selected in both columns.

[bibu]

Thanks for your suggestion, I will look into it. I'm guessing it's an IF function and you forgot the IF. As it is, it doesn't help much because the result will always be "selected", since either H or I will always be higher than 1.6, and the formula can't tell which one is the one that I am looking for (John Isner's odds in this example).
For example in row no. 4, John Isner's odds are 1.21 and the result will still be "selected" because his opponent's odds was greater than 1.6.

But the concept of adding another helper column I will go into it further and see if I can adapt it. Thanks!

[Richard Buttrey]

Yes, sorry about that, it should have started with =IF.

Here's and amendment for the helper column P

Please Login or Register  to view this content.

[bibu]

Hmm. the 4th row is still "selected". Anyway, this is what I found to be working :

Please Login or Register  to view this content.

Thanks for your time!

[Richard Buttrey]

Yes, there was a typo where I put H1 & I1 instead of course H2 & I2

[bibu]

Yeah I've just realized that myself would you say your solution is faster? Because the main sheet is already sluggish and I'd want to keep it as simple as possible.

[Richard Buttrey]

When you say sluggish, what do you mean? Are you referring to autofiltering Sheet1?

I can't see why it should be. Once the formulae have been populated they won't be recalculating and in any case there aren't that many formulae and the workbook is hardly any size at all, hence I'm puzzled.

[bibu]

No, no, this is a mini-sample of the main sheet, the main sheet has 20.000 rows and tons of other calculations. So when I'm using a button to auto-filter through all that data and recalculate everything.. it takes 5-10 seconds, especially since most of the formulae are done by me, and they're not nearly as simple as they could be.
Think

Please Login or Register  to view this content.

this and multiply it by fifty and add auto filtering through all of that, that's why it's sluggish

[Richard Buttrey]

OK,

The way to speed it up is to Range value all the formulae once you've created them. What I generally do is just keep the formulae on the first row so that when any data changes I can quickly copy it down and then copy and range value again.

This should return the speed top normal.

[bibu]

Hmm, I don't even know how to "range value" a formula, but I will open a thread about speeding things up on my sheet, some other time. Thanks again for your help.

[Richard Buttrey]

Hi,

Just select the whole range of formulae and then using the Ribbon menu pick the Paste...PasteSpecial....Values, select the top left hand cell in the range you're copying and click OK.

[bibu]

Done, cheers!

[barako]

great thank you