I have a text file ([url removed, login to view] , attached) with information for a large ballot (Meta-Categories, Categories, Sub-Categories, Ballot Items). I have cleaned it up, in terms of making sure things were in the right places, spelled correctly, etc. I have added *** to the beginning of any meta-categories, to make them easier to find. Categories are separated by a row of dashed lines. Sub-Categories (the islands) are a little trickier. There are 3 islands, but not every category has items for each island. Also, some ballot items have the name of an island within their name (although the sub-categories/islands are on lines by themselves).
I need someone to take this information and put it into an Excel spreadsheet, then assign ballot codes to each ballot item (more on this in the next paragraph). Each category needs to have a ballot item, called "Write In", as its' last ballot item. I have attached a file called [url removed, login to view], showing how I want the spreadsheet formatted. Each row of the spreadsheet needs to have all information for its' corresponding ballot item (Meta-Category, Category, Sub-Category, Ballot Item, Ballot Code).
Ballot codes are determined by applying the following rules:
1st digit represents the island (1 = St Thomas; 2 = St John; 3 = St Croix)
2nd, 3rd & 4th digits represent the category number
5th & 6th digits represent ballot item number within the category
So, if a ballot item were in the 347th category, on the island of St John, and it were the 12th ballot item within the category, its' ballot code would be 234712.
I am certain there is a way to easily complete this task using a script or macro, but that is beyond my skill level in using Excel.