I have a problem that I just want to check the viability of my solution for before I try and make it actually work....
I have a lot of address lists to work with at work. Each one is in a different format and each time they need to be used they need to be converted to a specific template format.
I can't just keep them once they are converted because they change from month to month (they are subscriber lists for magazines and journals) so each time I need to use one of them I need to convert it fresh from a fresh data download.
I can't get them sent to me in the correct format because there are well over 300 different customers, all of whom have established databases that run into their other systems and getting them all to change would be a nightmare (plus there are new customers coming in all the time)
So... I need to find some way to at least mostly automate the variable data coming in to go into a set structure that is the same every time. Within this structure the town, state/county and country all need to be in specific cells.
I have found Yahoo Placefinder which seems to me a good way to automate pulling out these fields as part of the xml string it outputs looks like the following:
Code: Select all
<city>Stevenage</city>
<county>Hertfordshire</county>
<state>England</state>
<country>United Kingdom</country>
<countrycode>GB</countrycode>
<statecode>ENG</statecode>
<countycode>HRT</countycode>
<uzip>AL4 0</uzip>
So in one row, columns A-M might be data, I would create a web address to call up the XML output by concatenating selected cells from that in column N, which would then automatically fill in columns O, P, Q and R with city, county, state, and country.
Then the plan would be to have a second worksheet where a deduplication setup is active to remove any of the information pulled by this XML query from the original cells in the data, and a third tab to copy/paste the final result into to do the final manual checking and refining stage. Then I can copy/paste that data into our other programs having cleaned it all up nicely but with a fraction of the mind-numbing manual checking that I have to do right now (and which is very error-prone for obvious reasons)
I'm not looking for a detailed guide as I know there are plenty out there right now for similar-ish tasks, but before I spend massive amounts of time trying to build a very complicated spreadsheet to do this I want to run it past a few people to check that what I'm going to ask the software to do is actually possible. Failing it being possible this way it seems to me my only other option would be to learn a programming language such as Python to the extent needed to get this done, and that's a whole barrel-load more work than it would be getting this stuff from Excel like this.
Fingers crossed you agree that this sounds technically possible and any handy tips for good websites to get more detailed excel help from for projects of this complexity (because as I say I'm not expecting that level of help on a fantasy football forum) would be much appreciated.