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

SINGAPORE EXPATS FORUM
Singapore Expat Forum and Message Board for Expats in Singapore & Expatriates Relocating to Singapore
The MS Excel Problem
Have a look around this site you may find something of use;
http://office.microsoft.com/en-us/training/default.aspx
Plavt.
http://office.microsoft.com/en-us/training/default.aspx
Plavt.
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).
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).
-
- Similar Topics
- Replies
- Views
- Last post
-
- 0 Replies
- 3707 Views
-
Last post by excelcycle
Sat, 24 Jun 2017 10:26 am
-
-
PR application problem.. need advise
by tanwll » Sat, 27 Feb 2016 7:56 pm » in PR, Citizenship, Passes & Visas for Foreigners - 2 Replies
- 1576 Views
-
Last post by tanwll
Sun, 06 Mar 2016 12:56 pm
-
-
-
Please help serious family problem Indian woman
by bhurirav » Fri, 25 Mar 2016 5:03 pm » in General Discussions - 77 Replies
- 22648 Views
-
Last post by sundaymorningstaple
Tue, 26 Apr 2016 10:51 pm
-
-
-
any recommendation for hair dandruff problem?
by Alica » Sat, 04 Mar 2017 1:23 pm » in Beauty, Health & Fitness - 13 Replies
- 5673 Views
-
Last post by abbby
Sat, 05 Dec 2020 10:11 am
-
-
-
any solution for quota problem
by Thiha Soe » Wed, 06 Sep 2017 3:04 pm » in PR, Citizenship, Passes & Visas for Foreigners - 4 Replies
- 1990 Views
-
Last post by Thiha Soe
Wed, 06 Sep 2017 6:18 pm
-
Who is online
Users browsing this forum: No registered users and 1 guest