The MS Excel Problem

[indiason]

Hello everyone,
Is there any Excel Master on this forum? I need some urgent help.
Here's my problem:
There are Two columns, the first represents product category (say products soap, brush, & comb), while the next column represents the serial nos. (say 1 to 10).
Each product can have serial nos 1 to 10, but once a combination is selected, it should not be repeated; for instance, if I select Soap+1 in a cell somewhere, the sheet should automatically reject the same entry elsewhere (i am going to use Data-Validation-List for this purpose).
The same should be the cas with Brush & Comb...
I tried using help, but to no avail.
Can you suggest a way in MS Excel in which it can be solved??
Your reply would be greatly appreciated.
regs,
Indiason

[Jedi]

Do you know how to code in VBA?

[indiason]

Hi Jedi,
Thanks for the reply.
No. I am not a regular programmer.. Please let me know what can be done best by me..
regs,
indiason

[Plavt]

Have a look around this site you may find something of use;


http://office.microsoft.com/en-us/training/default.aspx

Plavt.

[Jedi]

A VBA solution would be the best, but since you don't know the language, it's not plausible here.

The only other thing I can think of is using formulas in the worksheet to display some message when a duplicate value is detected. You could use the COUNTIF function to search for duplicates and the IF function to display the text.

Example: here's my sheet:

A B
1 a 1
2 b 1
3 b 2
4 a 1

So in my example, row 1 and 4 are duplicated.

In column C, Row 1, put this forumla:
=A1&":"&B1

In column D, Row 1, put this formula:
=IF(COUNTIF($C$1:$C$4,C1)>1,"duplicate value","")

Copy both formulas to Rows 2 to 4. Now you will get "duplicate value" appearing in rows 1 and 4. You can hide column C to make it transparent to the user. Also you'll need to modify the range in the formula column D formula (and copy the validation formulas down the worksheet). Perhaps you can just use the entire range of the sheet.

Still VBA would be neater (and more robust).

[indiason]

Thanks Jedi,
This solution is good, but not robust, as you put it. My real condition is that I have 100 products & each product would have 999 possibilities...
Can you suggest a simple script in VB, if feasible?
I'd be very grateful.
regs,
Indiason