entering info into different worksheets

[exiled]

Hi

I want to strip out a list of information in this case a phrase, all
from the same worksheet and place them in a different worksheet by
category. For example

if the list is like this:

current inflation rate
current UK inflation rate
sipps
uk inflation rate
uk interest rate predictions
alternative energy
gold prices
newspaper articles
price of silver
share prices today
uk interest rate forecast
gold price
buy gold
uk stock market
currency forecasts
HOUSE PRICE PREDICTIONS
HOUSE PRICE PREDICTION

I would want to put all the terms that include gold into one worksheet
and those with interest rate into another and so on. I would already
have the required worksheets created and named for example gold and
interest rate

How would I do this. With a formula or a macro.

Also I would want to strip out/delete the phrase from the original list
so I know what to do with the left overs.

If someone knows a formula or macro that would do this I would be most
grateful.

 

[Guest]

Here's one play which extracts/strips it out into 3 cols ..

A sample construct is available at:
http://www.savefile.com/files/8813588
Extracting Text

Source data assumed in A2 down

List the 2 phrases in B1:C1, ie: gold, interest rate
Put a label in D1: "leftovers"

Place in B2:
=IF(ROW(A1)>COUNT(E:E),"",INDEX($A:$A,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))
Copy B2 to D2

In E2:
=IF($A2="","",IF(ISNUMBER(SEARCH(B$1,$A2)),ROW(),""))
Copy E2 to F2

In G2:
=IF($A2="","",IF(AND(E2="",F2=""),ROW(),""))

(Leave E1:G1 empty)

Select B2:G2, fill down to the last row of data in col A

Cols B and C will return items from col A which contain the 2 phrases
indicated in B1:C1, while col D returns the "leftovers" from col A. All
extracts will be neatly bunched at the top.

Replace SEARCH with FIND in the criteria cols E and F if you want the phrase
search to be case sensitive. SEARCH is not case sensitive.

 

[exiled]

Hi Max

Many thanks for the tip.

Unfortunately it doesn't do what I require.

I need it to work over a selection of at least 30 groups and so the
phrase to be automatically deleted from the original list.

Do you have any ideas, another formula or macro.

 

[Guest]

I need it to work over a selection of at least 30 groups and so the

phrase to be automatically deleted from the original list.

Try this slightly revised approach to achieve the above ..

See sheet: Y in the attached sample:
http://www.savefile.com/files/6659952
Extracting_Text_Exceptions.xls

Source data is assumed within A2:A50

List the "exclusion" phrases within B1:B40, eg: gold, interest rate, etc
(I've catered for up to 40 exclusion phrases. Enter in any order within
B1:B40)

Put a label in C1: "leftovers"

Place in C2:
=IF(ROW(A1)>COUNT(D),"",INDEX($A:$A,MATCH(SMALL(D,ROW(A1)),D,0)))

In D2:
=IF($A2="","",IF(SUMPRODUCT(ISNUMBER(SEARCH($B$1:$B$40,$A2))*($B$1:$B$40<>""))>0,"",ROW()))

(Leave D1 empty)

Select C22, fill down to the last row of data expected in col A, i.e. to A50

Col C will return the required "leftovers", viz. items from col A which do
not contain the phrases indicated within B1:B40, with all extracts neatly
bunched at the top.

Replace SEARCH with FIND in the criteria col D if you want the phrase search
to be case sensitive. SEARCH is not case sensitive.

Adapt to suit ..

---

 

[Guest]

Typo in line:

Select C22, fill down to the last row of data expected in col A, i.e. to A50

"to A50" should read as: "to D50"

---