|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 15th, 2010, 07:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort
I have a spreadsheet with about a thousand lines of stock sales for the year.
I need to sort by this column alphabetically by the first word after the word "shares". DESCRIPTION 10,430.000 SHARES UIT FIRST TRUST TA 10,430.000 SHARES UIT FIRST TRUST TA 32.801 SHARES FRANKLIN TEMPLETON 7.347 SHARES CAPITAL WORLD BOND FUND 3.000 SHARES OF UIT FIRST TRUST 8.448 SHARES LOOMIS SAYLES BOND FUND 37.616 SHARES FRANKLIN TEMPLETON 14.221 SHARES LORD ABBETT FUND 7.000 SHARES OF UIT FIRST TRUST 8.919 SHARES LOOMIS SAYLES BOND FUND 44.000 SHARES UIT FIRST TRUST TARGET 8.454 SHARES DAVIS NEW YORK VENTURE 4.146 SHARES FRANKLIN TEMPLETON Any ideas how I could approach this? TYIA, Brandon 
|
|
#2
April 15th, 2010, 07:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sort
WORK WITH A COPY OF YOUR DATA
select all that data and press Ctrl-H search for Shares_ [the underscore means a space] replace with shares! and click Replace All Now, with all the data still selected (in a single column), press Alt-d and e (for Data-Text to columns) Choose delimited and the click Next Click in the Other checkbox and type in the ! sign Click Finish and you'll have 2 columns and you can sort on the second one "Brandon" wrote: I have a spreadsheet with about a thousand lines of stock sales for the year. I need to sort by this column alphabetically by the first word after the word "shares". DESCRIPTION 10,430.000 SHARES UIT FIRST TRUST TA 10,430.000 SHARES UIT FIRST TRUST TA 32.801 SHARES FRANKLIN TEMPLETON 7.347 SHARES CAPITAL WORLD BOND FUND 3.000 SHARES OF UIT FIRST TRUST 8.448 SHARES LOOMIS SAYLES BOND FUND 37.616 SHARES FRANKLIN TEMPLETON 14.221 SHARES LORD ABBETT FUND 7.000 SHARES OF UIT FIRST TRUST 8.919 SHARES LOOMIS SAYLES BOND FUND 44.000 SHARES UIT FIRST TRUST TARGET 8.454 SHARES DAVIS NEW YORK VENTURE 4.146 SHARES FRANKLIN TEMPLETON Any ideas how I could approach this? TYIA, Brandon
|
|
#3
April 15th, 2010, 07:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sort
It looks like some of the records contain 'SHARES' and some contain 'SHARES
OF'. In an empty column, enter this formula on the first row of data and adjust the A1 references as necessary: =IF(ISNUMBER(FIND("SHARES OF ",A1)),RIGHT(A1,LEN(A1)-FIND("SHARES OF ",A1)-9),RIGHT(A1,LEN(A1)-FIND("SHARES ",A1)-6)) Copy the formula down through all rows of data. Then select all the data and sort by the helper column with the above formula. Hope this helps, Hutch "Brandon" wrote: I have a spreadsheet with about a thousand lines of stock sales for the year. I need to sort by this column alphabetically by the first word after the word "shares". DESCRIPTION 10,430.000 SHARES UIT FIRST TRUST TA 10,430.000 SHARES UIT FIRST TRUST TA 32.801 SHARES FRANKLIN TEMPLETON 7.347 SHARES CAPITAL WORLD BOND FUND 3.000 SHARES OF UIT FIRST TRUST 8.448 SHARES LOOMIS SAYLES BOND FUND 37.616 SHARES FRANKLIN TEMPLETON 14.221 SHARES LORD ABBETT FUND 7.000 SHARES OF UIT FIRST TRUST 8.919 SHARES LOOMIS SAYLES BOND FUND 44.000 SHARES UIT FIRST TRUST TARGET 8.454 SHARES DAVIS NEW YORK VENTURE 4.146 SHARES FRANKLIN TEMPLETON Any ideas how I could approach this? TYIA, Brandon
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
