OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Is there a function for this

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Athalon
General User
General User


Joined: 10 Feb 2006
Posts: 40
Location: Michigan, USA

PostPosted: Wed Feb 15, 2006 9:44 pm    Post subject: Is there a function for this Reply with quote

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
Back to top
View user's profile Send private message
RealGrouchy
OOo Enthusiast
OOo Enthusiast


Joined: 25 Jan 2006
Posts: 144
Location: Ottawa, Canada

PostPosted: Wed Feb 15, 2006 9:55 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Feb 16, 2006 2:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Athalon
General User
General User


Joined: 10 Feb 2006
Posts: 40
Location: Michigan, USA

PostPosted: Thu Feb 16, 2006 5:16 am    Post subject: Thanks Much Reply with quote

Thanks much. I will try this and see. Matt
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group