I have a large excel spread sheet that I need converted into a sheet with a single row. I then need that data converted into a five column spreadsheet based on the data contained in the cells.
The datasheet is approximately 9200 rows and goes from Column A to Column FT. Each cell containts data in this format: "3152;Few vacancies - Willcox, AZ:"
Once the data is located in a single column(might have to take multiple pages as I am not sure about column limits in excel) I then need it converted into five columns of data. Each cell is already prepared for conversion. The five columns will be the ID number, delimited by the Semi-colon. The next will be the vacancies, delimited by the "-", followed by the City and State. The last two columns will be a copy of the previous, as I need an additional column for both the city and the state individually (so all three combinations).
There are some cells that have compound information in each section. For example there might be two cities listed in a cell: "3576;Few vacancies - West Oahu, HI, Central Oahu, HI:". In this case, two rows are required, one for each city, with the same ID number.
In the event that the cities are listed in all caps, convert them to proper capitalization (as per the excel formula).
The data in the columns needs to be cleaned up after conversion. By this I mean that there can be no additional spaces either before or after a piece of data.
Some of the cells might not contain data, and might look like "253;:". This data can be ignored. If there are duplicates (highly likely), then they should be removed as well.
I have attached a sample page with two columns. PLEASE REVIEW THE SAMPLE BEFORE MAKING A BID. ALSO, PLEASE ASK CLARIFICATION QUESTIONS BEFORE MAKING OR ACCEPTING A BID. THERE IS A CLARIFICATION FORUM FOR SUCH PURPOSES, SO PLEASE DO NOT BID ON THE PROJECT AND THEN SEND ME ENDLESS REQUESTS FOR CLARIFICATION. IF YOU DO NOT UNDERSTAND WHAT I AM ASKING, ASK NOW. IF YOU ACCEPT THIS JOB AND LATER DETERMINE YOU ARE UNABLE TO COMPLETE IT, I WILL LEAVE FEEDBACK INDICATING AS MUCH.
I am looking to get started ASAP. I don't have a specific time frame in mind as to when I need the results, but sooner is always better.