#Excel document of all cities in the uk update#

Although today’s Excel spreadsheets can handle 1,048,576 rows and 16,384 columns, developers at PHE used an older Excel file format (XLS instead of XLSX) resulting in each template being able to store only around 65,000 rows of data (or around 1,400 cases). These were then ingested into Excel templates to be uploaded to a central system to be made available to the Test and Trace team and government. The companies that analysed the swab tests to identify who had the virus submitted their results as comma-separated text files to PHE. And herein lies the problem.Īlthough Excel is popular and commonly used for analysis, it has several limitations that make it unsuitable for large amounts of data and more sophisticated analyses. And yet Public Health England used Microsoft Excel as an intermediary to manage a large volume of sensitive data. We have smart factories and smart cities we have self-driving cars and machines trained to exhibit human intelligence. We live in a world of big data, with artificial intelligence and machine learning permeating all aspects of our lives. We should not forget that the government and public health workers are doing an incredibly challenging and demanding job dealing with a pandemic. The issue was resolved with all new cases added to the totals reported over the weekend by breaking the data down across smaller spreadsheets.

#Excel document of all cities in the uk update#

The templates hit a limit in the number of rows they could handle and then failed to update with more cases added. To be more specific, the lab test results were being transferred to Excel templates. It emerged later that that day a “technical glitch” was to blame. Although all those who tested positive had been informed of their results, other people in close contact with them and potentially at risk of exposure were not immediately followed up (ideally within 48 hours). Not only does it lead to underestimating the scale of coronavirus in the UK, but perhaps more important is the subsequent delay in entering the details of positive cases into the NHS Test and Trace system which is used by a team of contact tracers. The missing figures were subsequently added to the daily totals, but given the importance of these numbers for monitoring the outbreak and making key decisions, the results of the error are far-reaching. For instance, you could require that they put in city and state / township / county / province / canton / whatever your part of the world calls them and then do a match using both those parameters.Public Health England has admitted that 16,000 confirmed coronavirus cases in the UK were missed from daily figures being reported between September 25 and October 2. In that case, you'd have to find some unique way of identifying which city you want. If you have multiple instances of cities it will be more complex as Jon Story already mentioned. You can get fancier than use by using tables - which I'm rather a fan of - but I was trying to give a simple example. I know that's silly, but I hope it makes sense. Alright, User, here's record N from that big list of countries you gave me. Index replies, 'Record N, you say? Fantastic.

There could be more but I'm going to stop here. And an exact match, right? OK, I've found the first exact match. Where should I look? Of course, right! I should look in Column A in the CityLookup tab. So I'm going to find the value in C1? Alright, that's a city name. Match jumps in and says, 'Don't worry, Index, I'll look that up for you. That has a big giant list of countries in it.

Ah, Column B from the tab CityLookup? Splendid. =INDEX(CityLookup!$B:$B,MATCH($C1,CityLookup!$A:$A,0))ġ) INDEX takes in an array of data and spits out the Nth value in that arrayĢ) MATCH looks for a certain value in an array of data and spits out where it's found (the 3rd parameter being 0 means that it looks for an exact match)ģ) Put it together and you get a little story: (indulge me)

In that case, you could use the following formula to pull in data: Let's also say that you have a table in another worksheet called CityLookup with Column A & B being the city and country names, respectively. Let's say that Column C has a city name and you want Column D to show you the country (or state or official motto or most popular hair color or whatever). As warned about in the comments, the simple solution relies on city names only appearing in your list once. I would recommend using a combination of INDEX and MATCH for your solution.