Searching a spreadsheet for specific items, then totalling into a small grid.

Giganews Newsgroups
Subject: Searching a spreadsheet for specific items, then totalling into a small grid.
Posted by:  Mike Barnard (m.barnard.trouse…@thunderin.co.uk)
Date: Tue, 29 Jul 2008

Hi.

Please excuse the length of this post.

I'm in a new job and my Excel skills are limited. I find myself
wanting to get it to do work I know it CAN do, but I don't know
exactly how. Excel 2003 on an XP PC.

I now keep track of a graffiti cleaning contract. I have to create
reports based on the information I recieve from a contractor.

I recieve reports in excel. It's not the ideal medium, but it's "how
it's done" **and what I'm stuck with**.

Once a month I recieve by email a workbook. Each Workbook has
worksheets for a weeks work. Each worksheet is made up of rows, each
representing an individual job that has been done. I need to read
these jobs and catagorise them. Then I want excel to do the counting
and put the results into a small grid.

So, a few example rows from the weekly sheet might look like this.
(Lots of other info snipped.)

Date      Address Property Graffiti    Sqm
Issued.                Type        Desc.

01/09/08 xxx    Sign        Tag            1
01/09/08 xxy    House    Swearing  4
01/09/08 xxz    Shop      Tag          .5
02/09/08 azz    Alley        Mixed        14
etc...

I have to read each row and using local knowledge and supplied
photographs of the graffiti, catagorise them and place them within one
of two geographical areas.

This was always done using scrap paper, and the results transferred by
hand to another spreadsheet that makes pie charts, LOTS of pie charts.
There is a lot of room for errors in the paper method. I want to move
this scrap paper onto the worksheet itself so that excel does the
worst of the work. My idea is this.

Date      Address Property Graffiti Sqm    A  B  C  D  E
Issued.                Type        Desc.

01/09/08 xxx    Sign        Tag          1
01/09/08 xxy    House    Swearing 4
01/09/08 xxz    Shop      Tag          .5
02/09/08 azz    Alley        Mixed      14
etc...

ABCDE are various catagories. So I look at each line, choose a
catagory and put a 1 or 2 in the relevant cell to represent the
geographical area.

Date      Address Property Graffiti Sqm    A  B  C  D  E
Issued.                Type        Desc.

01/09/08 xxx    Sign        Tag          1      2
01/09/08 xxy    House    Swearing 4                  1
01/09/08 xxz    Shop      Tag          .5                  2
02/09/08 azz    Alley        Mixed      14                      2
etc...

Now the formula!

I need a grid with ABCDE across and 1,2 down. I need the total square
metres in each grid of each catagory and area.

    A    B    C    D    E
1                      4
2  1                .5  14

I assume a VB routine along these lines...

for (row)
if A =1 then (somewhere in the grid) = (somewhere in the grid + sqm)
if B =1 then (somewhere in the grid) = (somewhere in the grid + sqm)
if C =1 then (somewhere in the grid) = (somewhere in the grid + sqm)
if D =1 then (somewhere in the grid) = (somewhere in the grid + sqm)
if E =1 then (somewhere in the grid) = (somewhere in the grid + sqm)
if A =2 then (somewhere in the grid) = (somewhere in the grid + sqm)
if B =2 then (somewhere in the grid) = (somewhere in the grid + sqm)
if C =2 then (somewhere in the grid) = (somewhere in the grid + sqm)
if D =2 then (somewhere in the grid) = (somewhere in the grid + sqm)
if E =2 then (somewhere in the grid) = (somewhere in the grid + sqm)
next row

I have used BASIC many years ago and a little Delphi (version5!), but
not VB. So, am I in the ballpark with the VB "code"? Where in the
sheet does the VB go? Any other tips please? Should I use CASE?

IF you got this far then your stamina does you credit, and I owe you
thanks!

Mike.

Replies