R
robertfhilliii
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?
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?