Duplicate Problem

I inherit an excel spreadsheet which contains the course schedule for a
college. It contains fields like course number, subject, section number,
days, times, room, instructor and credits. There is a unique number
assigned to each course offered. However, it sometimes happens that a given
course might have the same number listed more than once. For example, if the
class meets accross two rooms, it would be listed twice, yet have the same
course number. Or a course might have one listing for a lecture period and
another for a lab, and since it is the same course, it has the same course
number. However, the awkward part is that the credits assigned to each
course are tied to the course number, so when the course nunber is repeated,
as in the above examples, the credits are repeated, and this creates errors.
Using the example above: I have a biology class whose total credits is 4.
But when the lecture is listed, it shows 4 credits and when the lab is
listed, it shows 4 credits - because credits are tied to that class number.

What I need is some way to suppress the credits when the course number is
repeated. I don't know if this might be done with a query, if...then, or
report or filter or macro. As I say, I inherit the spreadsheet, so I can't
change that design. And it is updated periodically, so I don't want to make
lots of manual corrections each time it is updated. I am not a programmer,
so please couch any responses accordingly. Thanks for any help.

You could add a column to the table, and calculate the credits. For
example, with course number in column A, and credits in column E, enter
the following formula in row 2, and copy down to the last row of data:

=IF(COUNTIF(A$1:A2,A2)=1,E2,0)

Al wrote:
> I inherit an excel spreadsheet which contains the course schedule for a
> college. It contains fields like course number, subject, section number,
> days, times, room, instructor and credits. There is a unique number
> assigned to each course offered. However, it sometimes happens that a given
> course might have the same number listed more than once. For example, if the
> class meets accross two rooms, it would be listed twice, yet have the same
> course number. Or a course might have one listing for a lecture period and
> another for a lab, and since it is the same course, it has the same course
> number. However, the awkward part is that the credits assigned to each
> course are tied to the course number, so when the course nunber is repeated,
> as in the above examples, the credits are repeated, and this creates errors.
> Using the example above: I have a biology class whose total credits is 4.
> But when the lecture is listed, it shows 4 credits and when the lab is
> listed, it shows 4 credits - because credits are tied to that class number.
>
> What I need is some way to suppress the credits when the course number is
> repeated. I don't know if this might be done with a query, if...then, or
> report or filter or macro. As I say, I inherit the spreadsheet, so I can't
> change that design. And it is updated periodically, so I don't want to make
> lots of manual corrections each time it is updated. I am not a programmer,
> so please couch any responses accordingly. Thanks for any help.
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html