Matching
 

Dear all,

I'm working on a situation for making a rooming list, people sharing room.

I've around 60 classmate and some of them are going to share room with the
classmate. They change their mind so frequent, is annoying

I want to see how i can managed the change without any hassle.

I made use of their student number, then using vlookup to conver them into
real name.

For example;

Student number /Name
123 999 Eric Mary

When i type 123 in Col 1, col 3 show "Eric", type 999 in Col 2, col 4 show
"Mary". So, i know they are sharing the room.

But how can i alert the next entry , i can not type 999 again, as Mary has a
room mate already ?!

Can anyone help.. as when you deal with a list of 90 undecisive classmates

thanks
Eric

I came up with 2 ways to handle your situation:

(Assuming entries are in Cells A1:B???)
This one allows you to enter duplicate Student IDs, but flags them with a
colored cell background:
1)Select the Input Range
2)FormatConditional Formatting
3)Formula Is: =SUMPRODUCT(--($A$1:$B1=A1)*(A1<""))1
Format: ( I set the cell background color to bright yellow)
Click [OK]

This one prevents the entry of a duplicate Student ID:
1)Select the Input Range
2)DataValidationCustom
3)Formula: =SUMPRODUCT(--($A$1:$B1=A1)*(A1<""))=1
Click OK

Does that help?
--
Regards,
Ron

Ron,

I'm sorry, i'm too green to know the tick....i tried but unable to work it
out.

Eric


"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
I came up with 2 ways to handle your situation:

(Assuming entries are in Cells A1:B???)
This one allows you to enter duplicate Student IDs, but flags them with a
colored cell background:
1)Select the Input Range
2)FormatConditional Formatting
3)Formula Is: =SUMPRODUCT(--($A$1:$B1=A1)*(A1<""))1
Format: ( I set the cell background color to bright yellow)
Click [OK]

This one prevents the entry of a duplicate Student ID:
1)Select the Input Range
2)DataValidationCustom
3)Formula: =SUMPRODUCT(--($A$1:$B1=A1)*(A1<""))=1
Click OK

Does that help?
--
Regards,
Ron