Simple Excel Problem

[Scoops Bolling]

I've spent entirely too long today trying to figure out this seemingly easy issue without any degree of success, so I'm hoping SoSH's collective wisdom can quickly point out the issue. I have a spreadsheet with two columns, one of the actual data, and then a second with a formula to check if that data has the information I want, showing an "OK" if it does, "Not OK" if it doesn't (the formula for reference is: =IF(ISNUMBER(SEARCH("FR",A3)),"OK", "Not OK"), with that being for B1, so B2 is the same but for A4, B3 is A5, etc).
 
All I want to do is sort by my second column so that I can get a list of the "OK"s (as that's the only data I need), but every attempt at sorting the two just completely flops. I've tried grouping the rows, I've tried every sort option there is, I just can't get the damn thing to give me a list of "OK"s that I can copy and paste and get out of there. Is there some way to do this that I've missed? Is there another obvious way to get a list of the "OK"s? I feel like this can't possibly be that complicated, but I've run out of ideas.

 

[SirPsychoSquints]

Did you try using filter?  Highlight the header row and click "Filter" under "Data."

 

[Scoops Bolling]

Did you try using filter?  Highlight the header row and click "Filter" under "Data."

I have, and the results just come out all wonky. If I filter by the B column, the results don't seem to make any sense because it doesn't align as "OK" then "Not OK", it's seems more or less random.

 

[Gdiguy]

What excel version are you using? I just tried the same equation format in 2013 and it seemed to sort perfectly fine... my only thought is that you have a setting messed up somewhere, sort column B sorted on values should work
 
edit - you guys are totally right, the offset is what breaks it - I'd follow what grantb suggests below, what's breaking is that you're sorting Ax & Bx based on Bx, but Bx is then being re-calculated based on the new A(x-2)... but it's calculating based on the values that are 2 above, which are likely to be different than the values you had in Bx before

 

[mt8thsw9th]

Why wouldn't you just do in B1:
 
=IF(A3="FR","OK","Not OK")
 
And copy down? ISNUMBER and SEARCH seems redundant here.
 
edit - and in cases like this, after I get the data I need I will always copy/paste values. I've had some odds things happen when I'm sorting/filtering cells with formulas.

 

[NoXInNixon]

Copy - Paste Values ought to work.

 

[begranter]

Align the data so the formula referencing A3 is in B3; move the cell from B1 to B3, B2 to B4, etc.  Give the column a header in B2 just like you have one in A2.  Use filter on A2 and B2.
 
I don't think you can sort one column by another when the data doesn't line up in the rows.  It gets wonky because it's a formula and not values.
 
If that doesn't work, try using 1 and 0 instead of "OK" and "Not OK"

 

[gammoseditor]

Copy - Paste Values ought to work.

 
Yeah, this, then filter?

 

[Scoops Bolling]

Why wouldn't you just do in B1:
 
=IF(A3="FR","OK","Not OK")
 
And copy down? ISNUMBER and SEARCH seems redundant here.
 
edit - and in cases like this, after I get the data I need I will always copy/paste values. I've had some odds things happen when I'm sorting/filtering cells with formulas.

Because there's more data in the cell than just FR (there's 3 other pieces of data), and the FR segment is the only one that matters.
 

Align the data so the formula referencing A3 is in B3; move the cell from B1 to B3, B2 to B4, etc.  Give the column a header in B2 just like you have one in A2.  Use filter on A2 and B2.
 
I don't think you can sort one column by another when the data doesn't line up in the rows.  It gets wonky because it's a formula and not values.
 
If that doesn't work, try using 1 and 0 instead of "OK" and "Not OK"

Yep, this is it. It wasn't random, it was just recalculating everything in the B column after it had sorted, and I didn't realize the sort had worked until I checked it against the original spreadsheet.
 
Problem solved, thanks!

 

[mt8thsw9th]

Because there's more data in the cell than just FR (there's 3 other pieces of data), and the FR segment is the only one that matters.
 
Yep, this is it. It wasn't random, it was just recalculating everything in the B column after it had sorted, and I didn't realize the sort had worked until I checked it against the original spreadsheet.
 
Problem solved, thanks!

 
Alright, it wasn't really clear from the OP, but that makes sense (and the ISNUMBER and the offsetting cells threw me off, as I thought you might be offsetting for a specific reason). As I said, always copy/paste values in cases like this, even if cells are in the same row (it may be OCD, but it's better to safeguard against wonkiness).
 
However, I'd suggest this in similar searches:
 
=IF(IFERROR(SEARCH("FR", A3), 0), "OK", "Not OK")