| View previous topic :: View next topic |
| Author |
Message |
jimmy the saint
General User
Joined: 30 Nov 2007
Posts: 10
|
 Posted: Mon Jul 12, 2010 1:48 am Post subject: Isolate rows in which value of cell matches a list of terms |
 |
|
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 |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Mon Jul 12, 2010 3:10 am Post subject: |
|
|
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 |
|
|
jimmy the saint
General User
Joined: 30 Nov 2007
Posts: 10
|
| Posted: Mon Jul 12, 2010 10:44 am Post subject: |
|
|
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 |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Mon Jul 12, 2010 1:30 pm Post subject: |
|
|
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 |
|
|
|
