entering info into different worksheets

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"exiled" wrote:
>
> 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.
>
>
> --
> exiled
> ------------------------------------------------------------------------
> exiled's Profile: http://www.excelforum.com/member.php...o&userid=35619
> View this thread: http://www.excelforum.com/showthread...hreadid=553972
>
>

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.

> 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:D),"",INDEX($A:$A,MATCH(SMALL(D:D,ROW(A1)),D: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 C2:D2, 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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"exiled" wrote:
>
> 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.
>
>
> --
> exiled
> ------------------------------------------------------------------------
> exiled's Profile: http://www.excelforum.com/member.php...o&userid=35619
> View this thread: http://www.excelforum.com/showthread...hreadid=553972
>
>

Typo in line:
> Select C2:D2, fill down to the last row of data expected in col A, i.e. to A50

"to A50" should read as: "to D50"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---