I have a table listing > 100K service tickets, complete with ticket number, dates, customer, address info, etc.
For each record (service ticket), I want to classify the location of the job according to census data residing in other tables.
I did this successfully for our USA business....so well, in fact, that Management wants me to do the same for Europe.
I agreed to it but now I'm kind of regretting it. The US Census data by Zip Code is only 43,000 records long and there are no duplicates. The corresponding data set in Europe is 1.7million lines long and the same zip code might be present in 2 or more countries.
Since Excel 2016 can't list all of the european zips in one 'flat' list, the usual tricks involving concatenating (country code & zip) won't work. I felt 'forced' to list each country's zip and associated census data in its own table, i.e., zipcen_FRANCE, zipcen_ITALY, etc.
Economic regions in the EU are apparently called NATS; each NAT has many zips within it, and it is that classification that I need to assign to the tickets. The zip code (and country code) serves as a bridge between the tables.
SO you can see my dilemma:
Job 1234 might have been performed in Denmark and in zip "987 432". I want to add a column which looks up "987 432" and uses 'DK' (for denmark) in order to retrieve a value from the (obviously "zipcen_DENMARK") table. The very next line might by job 1235, which was performed in Spain and in zip "554433". My magical formula would use 'ES' (for spain) in order to retrieve a value from the (obviously "zipcen_SPAIN") table.
...and do this 100K times, once for each record.
I attempted to 'build' the table name within the formula as a string with brackets in quotes and the like, but was unsuccessful. Is there a function you can recommend that might overcome Excel's unwillingness to accept anything other than a manually referenced or linked table?
Thanks in advance!
For each record (service ticket), I want to classify the location of the job according to census data residing in other tables.
I did this successfully for our USA business....so well, in fact, that Management wants me to do the same for Europe.
I agreed to it but now I'm kind of regretting it. The US Census data by Zip Code is only 43,000 records long and there are no duplicates. The corresponding data set in Europe is 1.7million lines long and the same zip code might be present in 2 or more countries.
Since Excel 2016 can't list all of the european zips in one 'flat' list, the usual tricks involving concatenating (country code & zip) won't work. I felt 'forced' to list each country's zip and associated census data in its own table, i.e., zipcen_FRANCE, zipcen_ITALY, etc.
Economic regions in the EU are apparently called NATS; each NAT has many zips within it, and it is that classification that I need to assign to the tickets. The zip code (and country code) serves as a bridge between the tables.
SO you can see my dilemma:
Job 1234 might have been performed in Denmark and in zip "987 432". I want to add a column which looks up "987 432" and uses 'DK' (for denmark) in order to retrieve a value from the (obviously "zipcen_DENMARK") table. The very next line might by job 1235, which was performed in Spain and in zip "554433". My magical formula would use 'ES' (for spain) in order to retrieve a value from the (obviously "zipcen_SPAIN") table.
...and do this 100K times, once for each record.
I attempted to 'build' the table name within the formula as a string with brackets in quotes and the like, but was unsuccessful. Is there a function you can recommend that might overcome Excel's unwillingness to accept anything other than a manually referenced or linked table?
Thanks in advance!
