find multiple entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul H - 14 Oct 2006 22:39 GMT

Hi all
A colleague of mine has to sort out library numbers of children at our
school. This involves allocating up to 500 numbers individually for each
child. The system has been working for a number of years and each year he
has to delete children who have left and re allocate their numbers to a new
intake. What has happened over time is that numbers are either unused or
worse allocated to two or more children. What I would like to do is quickly
identify what numbers are a) unused and b) have multiple user names. I have
successfully played around with conditional formatting and spinners to
generate a sequence numbers to identify unused and multiple numbers, but
what I would really like to do is run a macro and have a table of unused and
multiple used numbers appear.
The data is very simple stored as:
pupil first name, pupil surname, library number.

Reply to this Message

Bernard Liengme - 14 Oct 2006 23:22 GMT

Assuming this data is on Sheet1:
1) On Sheet2 used Edit |Fill ->Series to get numbers 1 to 500 in A1:A500
2) On Sheet2 in B1 enter =IF(COUNTIF(Sheet1!C:C,Sheet2!A1)=0,"Free","")
3) Copy this down to B500 - double click the fill handle (solid square in
lower right corner of active cell)
Now you know the free numbers
4) On Sheet2 in C1 enter =IF(COUNTIF(Sheet1!C:C,Sheet2!A1)>1,"Multiple","")
and copy down to C500
Now you know the multiple numbers

Alternative formula
=IF(COUNTIF(Sheet1!C:C,Sheet2!A1)=0,A1,"")
=IF(COUNTIF(Sheet1!C:C,Sheet2!A1)>1,A1,"")
will list the free and multiple numbers in separate columns

best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hi all
> A colleague of mine has to sort out library numbers of children at our
[quoted text clipped - 10 lines]
> The data is very simple stored as:
> pupil first name, pupil surname, library number.

Reply to this Message