Extracting mixed values in single column to multiple colums
I am using Excel 2003 to gather data for my doctoral dissertation. I
have a huge data set of financial data for the state of Ohio that needs to be copied into a worksheet I created to analyze my data. Unfortunately, the worksheet that I am copying from is formatted as one column of data in column A. Several values, text and numerical, exist in each row. After each school district, there is a blank row to signify the next district. The sample of the data appears as follows in column A. The data does not have a specific format, nor does the numerical or text data line up in a way that "text to column" will work. The data spacing appears to be the same for each district: DISTRICT: Adams County/Ohio Valley Local COUNTY: Adams IRN: 061903 ADM, VALUATION & MILLAGE (1983-1984) SOURCE: SF12 Line 3 Basic ADM 4,916 Assessed value 480,468,317 Total ADM 5,769 Valuation/Basic ADM 97,736 Voted millage (Incl JVS) 20.80 Class1 eff tax rate 20.14 BASIC STATE AID (1983-1984) SOURCE: SF12 Adj Basic Aid 0.00 DPIA 957,432.00 State basic aid 1,675,815.24 Total Basic Support 2,633,247.24 CATEGORICAL SUPPORT (1983-1984) SOURCE: SF12 Vocational 1,024,384.31 Ext services 35,163.87 Special education 23,130.55 Transportation 588,949.20 Gifted 0.00 Total categorical 2,052,846.53 DBECN and EMR 381,218.60 TOTAL STATE SUPPORT (1983-1984) SOURCE: SF12 State basic support 2,633,247.24 Total SF-12 4,349,738.02 Total categorical 2,052,846.53 REVENUE BY SOURCE (1983-1984) SOURCE: FORM625 SOURCE (ALL FUNDS) AMOUNT PER PUPIL % OF TOTAL Federal 1,192,298.48 208.04 6.12 State 6,279,732.71 1,095.75 32.26 Local 11,995,695.45 2,093.13 61.62 Total Operating Revenue 19,467,726.64 3,396.92 100.00 Capital Project Funds 0.00 0.00 0.00 Total Receipts 19,467,726.64 3,396.92 100.00 Non-Revenue 0.00 0.00 0.00 EXPENDITURE PER PUPIL (1983-1984) SOURCE: FORM625 SOURCE (ALL FUNDS) AMOUNT PER PUPIL % OF TOTAL Instruction Regular 5,686,313.45 992.21 37.28 Special programs 1,698,627.86 296.39 11.14 Vocational 1,323,613.99 230.96 8.68 Other 0.00 0.00 0.00 Total 8,708,555.30 1,519.56 57.09 Support services Pupil and instr staff 771,176.07 134.56 5.06 Administration 1,449,107.11 252.86 9.50 Business, Central 3,826,929.71 667.76 25.09 Total 6,047,212.89 1,055.18 39.64 Non Instructional 71,620.37 12.50 0.47 Total current expenditures 14,827,388.56 2,587.24 97.21 Other expenditures Debt services 119,966.88 20.93 0.79 Capital outlay 158,047.72 27.58 1.04 Miscellaneous 148,048.37 25.83 0.97 Total 426,062.97 74.34 2.79 Grand total expenditures 15,253,451.53 2,661.58 100.00 RESOURCE AND EXPENDITURE PROFILE (1984) SOURCE: SM2 Ratio Percentile Ratio Percentile LOCAL REVENUE LIQUIDITY Real Estate 59.0 94.4 True Days Pers Tang 2.0 15.2 Cash 01/01/1984 26.0 38.5 Invest Earnings 2.2 66.6 True Days Other 0.3 8.1 Cash 12/31/1984 13.0 37.3 Total Local 64.0 82.4 Invest Earnings 2.0 67.2 STATE REVENUE EXPENDITURES Foundation Prog 32.0 20.2 Sal & Wages 55.0 32.8 Rollback Exempt 2.6 10.2 Fringe Benefits 15.0 77.1 Other 1.4 68.5 Sal & Benefits 70.0 46.6 Total State 36.0 17.6 Purch Services 6.6 15.9 Mat, Supp & Texts 5.8 86.0 FEDERAL REVENUE Capital Outlay 2.8 56.8 Total Federal 0.0 0.0 Other 3.1 98.3 BORROWING Sale of Notes 0.6 80.2 Redempt of Notes 1.3 80.8 I need to pull out specific numbers from this worksheet and paste them into a specific labeled column in a second worksheet. The data involves financial data for school districts in Ohio for the years 1980-1994. Copying and pasting back and forth is extremely time consuming. My second worksheet contains the following column headings: IRN, DISTRICT, COUNTY, ADM, Per Pupil Property Valuation, Federal Funds, Total Current Expenditure, Total, Per Pupil Expenditure. I want to copy the following values for each of the 600 plus districts in the state of Ohio from 1980-2004. Each year is on a separate worksheet: copy value for "valuation/adm" to "per pupil property valuation" copy value for "federal funds" to "federal funds" copy value for "total current expenditures" to "total current expenditures" Each value must match the correct district when copying from the original worksheet to my second worksheet. Can anyone suggest a visual basic code to complete this task? |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com