Consider yourself as a Data Analyst working for the ABC Analytics Corp. You are provided with data that contain the rankings of US states based on criteria such as Health Care, Education, Crime & Corrections, Infrastructure, Opportunity, Economy and Government printed in the same sequence for each state. The data was collected from several surveys and compiled for you in a text document named US States (US States.txt). Your manager is asking you to create a report based on this data, showing the Best Place Index and the Index Grade. The Best Place Index for a state is calculated from the sum of the ranks for all the given categories. The Best Place Index and the states performance are inversely proportional to each other, i.e. the lower the Best Place Index, the better the states performance. The Index Grade is categorized as given in the table below:

For example, if Oklahoma has the following rankings: Health Care 48, Education 30, Crime & Corrections 40, Infrastructure 42, Opportunity 36, Economy 34 and Government 31, then the Best Place Index is 261 (sum of all the rankings) and the Index Grade is C. Create a VBA sub program that generates a report using the following sequence of steps: Read the data from an external file US States.txt and store them as arrays. Calculate the Best Place Index and the Index Grade for each state. Rearrange the states in increasing order of Best Place Index. Print the Serial Number, State Name, Best Place Index and the Index Grade in the Excel cells. Print the same in the external text file named Report. Create a sub program to clear the Excel cells. The Excel report and the external file report should appear as below: (Pictures represent the actual answers)

i need help doing this vba excel excercise.

US States.txt Notepad File Edit Format View Help h,Alabama,47,47,42,31,40,45,42 2,Alaska, 33,34,47,25,28,50,41 3,Arizona, 28,43, 39,12,33,19,26 4,Arkansas, 50, 39,48,45,47,37,29 5,California,10,25,20,33,42,3,44 6,Colorado,11,18,26,7,31,1,13 Condition Index Grade 0