Is there a function for this

[Athalon]

Hi- I have many rows of data on a spreadsheet, only one column. Lets say A1:A900. I have performed the calculation I need to in the column next to A, which is B. Lets say there are approx. 100 cells in column B which match the criteria I have set. Each cell that matches the criteria has a "Yes" in it. Now I want to "pluck" only the Yes's from column B and consecutively move them to column C, so that each one is right on top of each other. Is there a way to do this? Thanks much, and I hope I explained this correctly. Matt

[RealGrouchy]

I'm a bit confused. Is this something that you can use the sort function for, by sorting first by column B, then by column A?

If A is not consecutive, you can add a third column (A-1) that is just a consecutive list of numbers, then sort by column B and column (A-1).

If that's not what you want, I think a more detailed explanation of the problem is in order.

- RG>
_________________
Quite simply, OOo Impress, does not.

XPsp2, OOo 2.3, SeaMonkey 1.1.7, IE v.6.6.6...

[RickRandom]

I would agree that the Sort looks like the best approach.

If you need to leave the original data untouched, you can copy the columns, maybe to another sheet, and Paste Special -> Strings or Numbers (don't know if you're using text or numbers), and then Sort. This might help if you want to have a set of results for each week.

If you want it to always update in "real time" whenever you change the data, you could add some extra columns:

column C would increase by 1 each time it is next to a Yes (with an suitable IF() function),

put =A1 in D1 and fill down,

put 1 to 100 (or however many Yes's you've got) in column E

in say cell F1 put =VLOOKUP(E1;$C$1:$D$100;2;0) so it looks for 1 in column C, and return the contents of column D (the duplicate of column A), then fill down column F.

(You have to do this duplicate of column A because LOOKUP() only looks to the right after it finds what it wants.)

It's all a bit OTT, I expect, so if the Sort will do what you want, stick with it.

If this doesn't make sense, try the Help on VLOOKUP() and if no help, ask here again.

[Athalon]

Thanks much. I will try this and see. Matt