I am hoping to automate three Excel tasks that we routinely perform. Right now it’s a bit tedious and repetitious to perform. I’m sure it requires some macro programming.
Basically, it’s for a marketing company that maintains client lists containing company name, contact, address, city, county, state and zip code, as well as phone number.
Here is the second task. (First task titled Automate Task – Data De-dupe posted earlier).
2) Automate Task - Street Order
In this task, we are concerned with the street order of our client lists. The street address is contained in a single field. I need to be able to sort this field by the street name. This is because our sales associates often embark on visits to our clients (personal meetings). Quite often there are multiple businesses within the same street to visit. Instead of bouncing back and fort and traveling all over the place, it would be ideal if they had a list in street order. For example, 123 Any St., followed by 124 Any St., followed by 125 Any St., etc., rather than 123 Any St., then 123 2nd St., then 456 3rd St., then 1234 Any St, etc.
Here’s how I manually accomplish this task right now, with my very amateur skills and knowledge. I’m sure there’s an easier way.
Fifteen separate sales associates provide daily feedbacks of client lists containing the above fields on an Excel spreadsheet. I copy all fifteen spreadsheets on to one master file/spreadsheet. I separate the address fields into pieces by highlighting the column, selecting Data à Text to Columns, and then using the wizard, selecting Delimited in Step 1, checking Space in Step 2, and then Finish. For example, this step will separate 123 Any St. Suite 456, contained in one single cell, into five separate cells.
I then re-merge fields two, three, four and five (i.e. the street address only without the unit no.), so that “123” is one cell and all of “Any St. Suite 456” is in another cell. This is accomplished using the formula =concatenate(cell2,“ ”,cell3, “ ”,cell4, “ ”,cell5).
I then sort the entire spreadsheet by zip code first, then street address, then unit no., in ascending order. Now I have a list in proper street order, in every city.
I didn’t mean to ramble on, I was hoping that this detailed description would also help you to understand what exactly it is that I want to accomplish. How do I automate this task? If there’s an easier way to accomplish any of this, I’m open to ideas.
Thank you very much.
Basically, it’s for a marketing company that maintains client lists containing company name, contact, address, city, county, state and zip code, as well as phone number.
Here is the second task. (First task titled Automate Task – Data De-dupe posted earlier).
2) Automate Task - Street Order
In this task, we are concerned with the street order of our client lists. The street address is contained in a single field. I need to be able to sort this field by the street name. This is because our sales associates often embark on visits to our clients (personal meetings). Quite often there are multiple businesses within the same street to visit. Instead of bouncing back and fort and traveling all over the place, it would be ideal if they had a list in street order. For example, 123 Any St., followed by 124 Any St., followed by 125 Any St., etc., rather than 123 Any St., then 123 2nd St., then 456 3rd St., then 1234 Any St, etc.
Here’s how I manually accomplish this task right now, with my very amateur skills and knowledge. I’m sure there’s an easier way.
Fifteen separate sales associates provide daily feedbacks of client lists containing the above fields on an Excel spreadsheet. I copy all fifteen spreadsheets on to one master file/spreadsheet. I separate the address fields into pieces by highlighting the column, selecting Data à Text to Columns, and then using the wizard, selecting Delimited in Step 1, checking Space in Step 2, and then Finish. For example, this step will separate 123 Any St. Suite 456, contained in one single cell, into five separate cells.
I then re-merge fields two, three, four and five (i.e. the street address only without the unit no.), so that “123” is one cell and all of “Any St. Suite 456” is in another cell. This is accomplished using the formula =concatenate(cell2,“ ”,cell3, “ ”,cell4, “ ”,cell5).
I then sort the entire spreadsheet by zip code first, then street address, then unit no., in ascending order. Now I have a list in proper street order, in every city.
I didn’t mean to ramble on, I was hoping that this detailed description would also help you to understand what exactly it is that I want to accomplish. How do I automate this task? If there’s an easier way to accomplish any of this, I’m open to ideas.
Thank you very much.
