Singapore Expats Forum

The MS Excel Problem

Discuss about computers & Internet. Including mobile phones, home appliances & other gadgets. Read about Windows security risks or virus updates.
indiason
Newbie
Newbie
Posts: 5
Joined: Fri, 07 Apr 2006

The MS Excel Problem

Postby indiason » Thu, 18 May 2006 8:17 am

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

User avatar
Jedi
Regular
Regular
Posts: 76
Joined: Tue, 13 Sep 2005

Postby Jedi » Thu, 18 May 2006 1:43 pm

Do you know how to code in VBA?

indiason
Newbie
Newbie
Posts: 5
Joined: Fri, 07 Apr 2006

Postby indiason » Thu, 18 May 2006 3:42 pm

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

User avatar
Plavt
Director
Director
Posts: 4292
Joined: Wed, 18 May 2005
Location: United Kingdom

Postby Plavt » Thu, 18 May 2006 4:31 pm

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


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

Plavt.

User avatar
Jedi
Regular
Regular
Posts: 76
Joined: Tue, 13 Sep 2005

Postby Jedi » Thu, 18 May 2006 4:50 pm

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
Newbie
Newbie
Posts: 5
Joined: Fri, 07 Apr 2006

Postby indiason » Thu, 18 May 2006 7:43 pm

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


  • Similar Topics
    Replies
    Views
    Last post

Return to “Computer, Internet, Phone & Electronics”

Who is online

Users browsing this forum: No registered users and 3 guests