Extracting data

[Gary Thomson]

I realise that this is an extremely long post, but I hope
someone out there knows the answer as it will save my
company several thousands of pounds per year!

I have the following set up in Excel (a list of Units in
Column A, and a list of days of the month in Row 1):

A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths
3 English
4 Music
5 P.E.
6 Geography
7 History
8 Drama
9 Science
.. .
.. .
.. .

These are all Classrooms in a School. On any given day, a
room can be Available or Unavailable.

If a room is Available, that means the pupils are able to
be taught in it, and there is no entry placed in it's
corresponding cell (e.g. suppose that the Maths room is
Available on the 3rd Feb, then cell D2 would be blank).

If a room in Unavailable, the pupils could not be taught
in the room and the firm that are running the school are
penalised as a result. The type of fault that caused the
Unavailability of the room is entered into the
corresponding cell (e.g. suppose the Science room was
Unavailable on the 1st Feb due to a burst water pipe
(which is labelled as FAULT "a"), then we would enter "a"
into cell B9). Thus the above array would perhaps look as
follows:


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

So in the above, Fault "a" has caused Unavailability in
the Maths Classroom on 1st & 2nd Feb, in the English
Classroom on 1st, 2nd & 3rd Feb, in the Music Classroom on
1st & 3rd Feb, and in the Science Classroom on 9th Feb.

Similarly, Fault "b" has caused Unavailability in the
English Classroom on 2nd & 3rd Feb, and in the Music
Classroom on 1st, 2nd & 3rd Feb, and so on.

All of the above would be included in a worksheet
called "UNAVAILABILITY".

Now, if a room is Unavailable on a given day, a penalty
applies, dependant on the size of the room. For example,
if the Maths Classroom is Unavailable, then the penalty is
£12 per day (no matter what type of fault caused the
Unavailability). Similarly, if the History Classroom is
Unavailable, the Deduction is £8 (again, no matter what
type of fault caused the Unavailability), since this room
is slightly smaller than the Maths Classroom. These
values could be stored in a separate column, say column AZ.

The problem I have is that within this set-up there is a
clause which states that "The minimum aggregate deduction
for a fault is £50". Thus for fault "a", if the total of
all the deductions applying for this fault is less than
£50, then we reset the total to £50. If the total is
already more than £50, it remains at that level.

So what I need to do is pull out all the deductions that
relate to fault "a", and check whether the total of these
is less than £50. Similarly, I need to do this for all
the deductions for fault "b", and so on. How can Excel do
this?

 

[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 this is an extremely long post, but I hope
someone out there knows the answer as it will save my
company several thousands of pounds per year!

I have the following set up in Excel (a list of Units in
Column A, and a list of days of the month in Row 1):

A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths
3 English
4 Music
5 P.E.
6 Geography
7 History
8 Drama
9 Science
.. .
.. .
.. .

These are all Classrooms in a School. On any given day, a
room can be Available or Unavailable.

If a room is Available, that means the pupils are able to
be taught in it, and there is no entry placed in it's
corresponding cell (e.g. suppose that the Maths room is
Available on the 3rd Feb, then cell D2 would be blank).

If a room in Unavailable, the pupils could not be taught
in the room and the firm that are running the school are
penalised as a result. The type of fault that caused the
Unavailability of the room is entered into the
corresponding cell (e.g. suppose the Science room was
Unavailable on the 1st Feb due to a burst water pipe
(which is labelled as FAULT "a"), then we would enter "a"
into cell B9). Thus the above array would perhaps look as
follows:


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

So in the above, Fault "a" has caused Unavailability in
the Maths Classroom on 1st & 2nd Feb, in the English
Classroom on 1st, 2nd & 3rd Feb, in the Music Classroom on
1st & 3rd Feb, and in the Science Classroom on 9th Feb.

Similarly, Fault "b" has caused Unavailability in the
English Classroom on 2nd & 3rd Feb, and in the Music
Classroom on 1st, 2nd & 3rd Feb, and so on.

All of the above would be included in a worksheet
called "UNAVAILABILITY".

Now, if a room is Unavailable on a given day, a penalty
applies, dependant on the size of the room. For example,
if the Maths Classroom is Unavailable, then the penalty is
£12 per day (no matter what type of fault caused the
Unavailability). Similarly, if the History Classroom is
Unavailable, the Deduction is £8 (again, no matter what
type of fault caused the Unavailability), since this room
is slightly smaller than the Maths Classroom. These
values could be stored in a separate column, say column AZ.

The problem I have is that within this set-up there is a
clause which states that "The minimum aggregate deduction
for a fault is £50". Thus for fault "a", if the total of
all the deductions applying for this fault is less than
£50, then we reset the total to £50. If the total is
already more than £50, it remains at that level.

So what I need to do is pull out all the deductions that
relate to fault "a", and check whether the total of these
is less than £50. Similarly, I need to do this for all
the deductions for fault "b", and so on. How can Excel do
this?