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.
Post Reply
indiason
Newbie
Newbie
Posts: 5
Joined: Fri, 07 Apr 2006 5:21 pm

The MS Excel Problem

Post by 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 8:11 pm

Post by 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 5:21 pm

Post by 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 2:13 pm
Location: United Kingdom

Post by 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 8:11 pm

Post by 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 5:21 pm

Post by 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

Post Reply
  • Similar Topics
    Replies
    Views
    Last post

Return to “Computer, Internet, Phone & Electronics”

Who is online

Users browsing this forum: No registered users and 1 guest