Sorting Data

[statsfun]

Hello there

I have an issue regarding sorting a large volume of data.

Basicaly i want to filter by a particular value in column A. However this value (X) appears many times but its only certain X values that i want as they are the final values of a particular iteration.

The only way of distinguishing these values is by the fact that immediately after a required value, there is a value F in the cell immediately below. So, for example, A299 = X and A300 = F, well this is value of X that i want.

Is there any formula i can use or anything else i can do to sort like this? I waas thinking of some kind of IF statement but havent found anything to work.

Cheers for any help.

The attached example shows what i mean. I want the value of X where A299 = X and A300 = F and not for example A288. Keep in mind that this is essentially one converged iteration and i have 1000's of these in one file hence why im looking to filter/sort by the criteria i have specified here.

[Blake 7]

do you want a simple count of all instances of Y being followed by D?

If you do want to filter, where there is an instance of y being followed by D which row are you interested in? the y or d?

[statsfun]

No, i need the values of Y in the respective row. The values of Y that i require are just followed by D as i described. So ideally i want to filter/sort the data and end up with Y values i need. I dont mind if the D values were there too, easy to take them out.

[Blake 7]

Data > Filter then select Y from the filter drop down

[statsfun]

If only it was that simple. I dont think you are getting what im asking. I will post some data to illustrate the question im asking.

[Blake 7]

hey - i'll be around until 21:45 if you post after that i'll take a look in the morning.

[WorldBridge]

I think it depends on what F is. You should be able to do it with an IF statement though I think.

Is F always a certain value or is it just different from X?

Could you post an example of what you need before and after?

[statsfun]

Added some information and example to initial post

[WorldBridge]

Ok, now I'm less sure what you're asking. Cell A299 = 'X' As in the letter X not an actual value.
Are you looking for an actual value to go there?

What value do you want that cell to be? Where does that come from?

Added some information and example to initial post

[Blake 7]

See attached for one way of doing it. I have used a helper colunm as you will see, and then filtered on that column. There are many more ways to skin this cat.

[statsfun]

I want the line of data that goes with X in cell A299 so essentially all of row 299. Now obviously when filtering column A by X, you attain quite a few but its still obvious which one is desired as its the last one. When these values are immersed in 1000's of iterations the only way of distinguishing them is by the fact that the required X values are immediately followed by an F value in the cell below.

[WorldBridge]

Ahh I see now. Ok here is my solution:

Insert a new column A and add this formula to cell A1 =IF(B300="F","X","")
Then copy all the way down. This will make that cell "X" if the cell one over and down is "F" and blank otherwise.
(You can play with it if you need to)

Then building on Blake 7's idea you just filter column A for X and voila!

Example.xlsx