Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
entering info into different worksheets
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
entering info into different worksheets
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
entering info into different worksheets
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
entering info into different worksheets
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a workbook that the worksheets update info | Excel Worksheet Functions | |||
Adding same cells across multiple worksheets | Excel Worksheet Functions | |||
Transfer Info From Worksheets | Excel Discussion (Misc queries) | |||
Can a formula operate between worksheets, ie collating info from . | Excel Worksheet Functions | |||
Adding rows of different info from separate worksheets into summar | New Users to Excel |