Extracting Data (Updated)

[Gary Thomson]

I realise that in my earlier post it may have been too
much to read so I have tried to simplify it:

Suppose I have the following set-up

UNAVAILABILITY
A B C D E F ....
1 Unit Amount 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths £10 a
3 English £11 a ab b
4 Music £8 abc b abcde
5 P.E. £9
6 Geography £6 a
7 History £6
8 Drama £7
9 Science £12 a
... .
... .
... .

Now suppose that I want to add up the amount that is
attributed to "a" i.e:
£10 + £11 + £11 + £8 + £8 + £6 + £12 = £66.

Similarly, the total amount attributed to "b" i.e:

£11 + £11 + £8 + £8 + £8 = £46.

"c": £8 + £8 = £16.

"d": £8.

"e": £8.

How would I do this?

Can I use a for.. from...do loop?

 

[Tom Ogilvy]

Dim cell as Range, Tot as Double
for each cell in Range("C1:F9")
if instr(cell,"a") then
tot = tot + cells(cell.row,2).Value
end if
Next

--
Regards,
Tom Ogilvy



I realise that in my earlier post it may have been too
much to read so I have tried to simplify it:

Suppose I have the following set-up

UNAVAILABILITY
A B C D E F ....
1 Unit Amount 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths £10 a
3 English £11 a ab b
4 Music £8 abc b abcde
5 P.E. £9
6 Geography £6 a
7 History £6
8 Drama £7
9 Science £12 a
... .
... .
... .

Now suppose that I want to add up the amount that is
attributed to "a" i.e:
£10 + £11 + £11 + £8 + £8 + £6 + £12 = £66.

Similarly, the total amount attributed to "b" i.e:

£11 + £11 + £8 + £8 + £8 = £46.

"c": £8 + £8 = £16.

"d": £8.

"e": £8.

How would I do this?

Can I use a for.. from...do loop?

 

[Dan E]

Gary,

If your not too set on a programming solution and worksheet functions
will suffice.

=SUMPRODUCT((ISERROR(SEARCH(R1,R2))=FALSE)*(R3))

Where:
R1 is a cell containing the code to be found (ie a)
R2 is that may contain the code (in your example C2:F9)
R3 is the range containing the amount (in your example B2:B9)

So if you had your data in A1:L9 and starting in A12 your summary

Code Total
a 66
b 46
c 16
d 8
e 8

The formula in B13 should be
=SUMPRODUCT((ISERROR(SEARCH(A13,$C$2:$L$9))=FALSE)*($B$2:$B$9))
which could be dragged down through B17 to get all codes.

Dan E

I realise that in my earlier post it may have been too
much to read so I have tried to simplify it:

Suppose I have the following set-up

UNAVAILABILITY
A B C D E F ....
1 Unit Amount 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths £10 a
3 English £11 a ab b
4 Music £8 abc b abcde
5 P.E. £9
6 Geography £6 a
7 History £6
8 Drama £7
9 Science £12 a
... .
... .
... .

Now suppose that I want to add up the amount that is
attributed to "a" i.e:
£10 + £11 + £11 + £8 + £8 + £6 + £12 = £66.

Similarly, the total amount attributed to "b" i.e:

£11 + £11 + £8 + £8 + £8 = £46.

"c": £8 + £8 = £16.

"d": £8.

"e": £8.

How would I do this?

Can I use a for.. from...do loop?