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

Isolate rows in which value of cell matches a list of terms

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


Joined: 30 Nov 2007
Posts: 10

PostPosted: Mon Jul 12, 2010 1:48 am    Post subject: Isolate rows in which value of cell matches a list of terms Reply with quote

I have several text files with a bunch of terms and a spreadsheet with tons more. I need to add information to each row in which a certain cell matches that list of terms.

So, for example, ROW1 contains a cell which matches TERM in my list, that row, and others which match, will be isolated or grouped so that I can quickly add the data. The only alternative is to do it by hand, which will be approx 500 edits.

Is there a way to filter or sort based on a separate text file of terms? Thanks
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Jul 12, 2010 3:10 am    Post subject: Reply with quote

Maybe somethng like this...
http://www.4shared.com/file/f9-Xdb5l/Sort_out_rows_with_text_from_l.html
Data in A1:K10.
List of terms in P1:P6.
Array formula in L1
Code:
IF(SUM(IF(COUNTIF($A1:$K1;$P$1:$P$6)>0;1;0))>0;1;"")
first produces an array of the the number of times that each term in P1:P6 is found in A1:K1. It then sums the numbers in that array. If the sum is greater than 0 then one or more of the terms in the list occurs in the first row of the A to K data, and the array formula returns 1, otherwise it returns a blank.
Ctrl+Shift+Enter key combination must be used when entering array formulas.
The L1 array formula has been filled down to the last (10th) row. When array formulas are filled down the Ctrl key must be pressed while the fill handle is dragged, alternatively the L1 formula can be copy/pasted into the lower cells.
With the results of the formula in place, A1:L10 can be selected then sorted by column L descending to get all the rows with a 1 in column L to the top of the sheet.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jimmy the saint
General User
General User


Joined: 30 Nov 2007
Posts: 10

PostPosted: Mon Jul 12, 2010 10:44 am    Post subject: Reply with quote

Hey thanks man! I have been playing around with how it works and I think I kind of understand what's going on there.

One problem that I am having, though, is that it gives quite a few false positives due to pattern matches within words. For example, the search terms "in" and "for" produces a false positive in the row which contains the word "INFORmation."

Is there a way to make the search terms only produce exact matches (ignoring case) or is that asking too much of a limited tool?

Thanks again,

JTS
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Jul 12, 2010 1:30 pm    Post subject: Reply with quote

Oops!
Go Tools|Options... to bring up "Options" dialog.
If necessary, click on the little + to expand "OpenOffice.org Calc" then click on "Calculate" then put a tick in the box for "Search criteria = and <> must apply to whole cells". Then OK.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
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