|  | Searching a spreadsheet for specific items, then totalling i | | |
| | | Mike Barnard |  |
|  Posted: Tue Jul 29, 2008 5:29 pm Post subject: Searching a spreadsheet for specific items, then totalling i |  |
| |  | |
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. |
| |
| | | Pete_UK | |
| | Posted: Tue Jul 29, 2008 7:53 pm Post subject: Re: Searching a spreadsheet for specific items, then totalli | |
| | | |
You don't need VBA to do this - simple formulae can do it for you.
First of all some assumptions based on what you've told us:
1. Assume the sheet that contains the data is named Sheet1
2. Assume your extra columns (for categories A to E) are F to J on
Sheet1
3. Assume you Insert a new sheet, and rename it to Summary
4. Assume your grid on the Summary sheet occupies A1:E2
Then you can put this formula in A1 of the Summary sheet:
=SUMIF(Sheet1!F:F,ROW(A1),Sheet1!$E:$E)
Then you can copy this cell into A2, and then copy A1:A2 across into
columns B to E.
This will give you your grid.
The SUMIF function is a conditional SUM, i.e. add up all the items in
column E of Sheet1 where column F of Sheet1 is the same as ROW(A1)
(i.e. 1). When the formula is copied across it becomes, in B1:
=SUMIF(Sheet1!G:G,ROW(B1),Sheet1!$E:$E)
and so this will look at column G in Sheet1, equivalent to your B
category. When the formula is copied down, the only thing that changes
is the ROW part, which is basically just returning the number of that
row (which ties in with your geographical area).
Hope this helps.
Pete
On Jul 29, 8:29 pm, Mike Barnard <m.barnard.trous...@thunderin.co.uk>
wrote:
| Quote: | 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. |
|
| |
| | | Mike Barnard | |
| | Posted: Wed Jul 30, 2008 3:53 pm Post subject: Re: Searching a spreadsheet for specific items, then totalli | |
On Tue, 29 Jul 2008 12:53:35 -0700 (PDT), Pete_UK
<pashurst@auditel.net> wrote:
| Quote: | You don't need VBA to do this - simple formulae can do it for you.
First of all some assumptions based on what you've told us:
1. Assume the sheet that contains the data is named Sheet1
2. Assume your extra columns (for categories A to E) are F to J on
Sheet1
3. Assume you Insert a new sheet, and rename it to Summary
4. Assume your grid on the Summary sheet occupies A1:E2
Then you can put this formula in A1 of the Summary sheet:
=SUMIF(Sheet1!F:F,ROW(A1),Sheet1!$E:$E)
|
Hi.
Thanks, that was a very useful snippet. Now to make the rest of it
work!  |
| |
| | | Pete_UK | |
| | Posted: Wed Jul 30, 2008 5:59 pm Post subject: Re: Searching a spreadsheet for specific items, then totalli | |
You're welcome, Mike - thanks for feeding back.
Pete
On Jul 30, 6:53 pm, Mike Barnard <m.barnard.trous...@thunderin.co.uk>
wrote:
| Quote: | On Tue, 29 Jul 2008 12:53:35 -0700 (PDT), Pete_UK
pashu...@auditel.net> wrote:
You don't need VBA to do this - simple formulae can do it for you.
First of all some assumptions based on what you've told us:
1. Assume the sheet that contains the data is named Sheet1
2. Assume your extra columns (for categories A to E) are F to J on
Sheet1
3. Assume you Insert a new sheet, and rename it to Summary
4. Assume your grid on the Summary sheet occupies A1:E2
Then you can put this formula in A1 of the Summary sheet:
=SUMIF(Sheet1!F:F,ROW(A1),Sheet1!$E:$E)
Hi.
Thanks, that was a very useful snippet. Now to make the rest of it
work! |
|
| |
|
|
