Best Way to do it

[Neil Greenough]

I am currently a police officer in the UK and I am trying to device a
spreadsheet / database that will highlight problem hotspots.

Now, I intend to create a spreadsheet that will highlight areas that are
affected by different crime. So, in one sheet I will have burglaries, car
crime in another, antisocial behaviour in another.

Now I am unsure what data the spreadsheet should contain and how to display
the results. I would like the results to come back on an individual name /
address basis per sheet in the file, but I would also like it to produce
results on road names.

So for example, if Mr Jones lives in 123 High Street, I would want the
spreadsheet to highlight it if Mr Jones gets burgled on two or more
occasions. Nonetheless, I would also like some results produced if two or
more burglaries are recored in High Street, regardless of the house number.

How would I go about this? Can I search cells in a column for specific
words, so in the above case, "High Street" regardless of number?

Many thanks

 

[Pete]

The design of your spreadsheet is very important - try to list all the
things you might want to report on and then ensure that you capture
this data in columns on your main sheet. You could split the address so
that you have separate fields (columns) for house number, street,
district, town, county, postcode etc so that you can report on each of
these if you wish. Where you do have combined fields you can use the
function COUNTIF( ) and use wildcard characters to mask out the bits
you do not need to compare (e.g. *High St* in your example). The second
asterisk enables you to treat High Street and High St. as the same,
where you might not have been totally consistent with your data input.

Hope this helps,

Pete

 

[CLR]

All of what Pete has said is good and true. In addition, I would recommend
you looking at the AutoFilter feature which can help a lot in separating the
data for better examination. As well, depending on the enormity of your
project, you might consider engaging the services of a Professional who could
provide you with a map of your area, "lit up" in real time in accordance with
your "crimes list" for a real Visual Report.


Vaya con Dios,
Chuck, CABGx3

 

[Roger Govier]

Hi Neil

Provided I get exemption from all future speeding and parking offences, I
would be very happy to assist<vbg>.

Now being serious, you have received very good advice already, the only
thing I would want to emphasise is that it would be best to keep all type of
crime on the same sheet, rather than splitting across 3 sheets. An
additional column, which Identifies either Burglaries, Car crime, ASBO etc
could be used to filter the list to show just one of the categories if
required, and would make it easier to see if High Street is bad for Car
crime and Burglaries etc., without having to resort to complicated multi
sheet formulae.

If you want to mock up what you are thinking and mail it to me, I would be
pleased to take a look and make any suggestions for improvement. To mail
direct, remove NOSPAM from my email address.

Regards

Roger Govier

 

[jaf]

Hi Neil,
Goto http://maps.google.com/ and enter "downing street, UK" in the input
box. Click search.

You could end up with something like this...
http://www.chicagocrime.org/

 

[Neil Greenough]

Many thanks for your replies.

We do have systems which light up crimes and you can filter the system on a
date range and area etc....

I'm interested about the map stuff though.....sounds really good and could
be used for more future projects.

Many thanks

 

[HS Hartkamp]

A strongly agree with the previous advice to make one large list. This gives
you more flexibility.

To make sure people type the different categories correctly, you can use
excel's option DATA VALIDATION and make a list with possible values
somewhere on the same sheet.

To the look at the results, I think the best way is to create one or more
pivot-tables. These can give you quick insights into different cross
sections of your data. Also, this makes graphs that can be changed as you
look at them. Powerful stuff.

Two restraints:
- The maximum number of crimes is limited by the sheet-length (just over
60.000 rows)
- After adding crimes, you need to refresh the pivot table to see the added
results.

Bas Hartkamp.