Excel crashes on saving

mathetes 

Hello mathetes

Thanks for responding so positively.

Each month the 43 Police Forces in the UK release crime data and this is classified by 10 “crime types” such as “Violence against the person” and “Robbery”.

Every single crime report has the Latitude & Longitude of its location.

Obviously my first filter is to look at each force separately.

Taking “Thames Valley Police” as an example. The data is made available, freely, is a CSV file.

Step 1: Translate the Lat/Lon into location files. In the UK we define geographic areas with LSOA Codes. Each LSOA Code covers about 1500 of the population.

I use a third party site to translate the codes and insert these into the csv file, which is then saved as a macro enabled Excel file and now begin the work.

Step 2: The number of crimes in each LSOA area varies from 0 to an unknown (so far) maximum. I need to produce an analysis of each crime type in each and every LSOA area. This second step entails translating each LSOA code into a unique format so that I can use VLOOKUP. I use a HELPER column to do this with the LSOA Codes in Column “E”.

=E2&COUNTIF($E$2:E2,E2)

Step 3: Having established that each crime is now associated with a unique modified LSOA code this step looks up the crime and reports it into the first available column. There is an issue here that I do not know how many columns will be required. Consequently I extend the range to 100 columns and then check that there is a final column with all null values. If necessary the number of columns is extended until this condition is satisfied.

=IFERROR(VLOOKUP($E2&COLUMNS($L$1:L1),$G$2:$K$18306,5,0),0)

This is extended across the 100 columns and down the thousands of rows.

It is this step that causes the crash! Just too many calculations.

I use the null result as a “0” so as to see if I need additional columns.

Step 4: Each of the 10 crime types is then allocated a column and we “COUNTIF” for each crime type.

Step 5:  Once we have the results it’s relatively easy to then produce reports.

Thanks for reading.

JohnB