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
-
- 1 Replies
- 2669 Views
-
Last post by Evangline.s
Tue, 14 Aug 2018 9:46 am
-
- 4 Replies
- 5287 Views
-
Last post by joeyy
Fri, 04 Oct 2019 1:36 pm
-
- 0 Replies
- 867 Views
-
Last post by mummia20
Fri, 25 Jan 2019 2:19 pm
-
- 3 Replies
- 2132 Views
-
Last post by Mad Scientist
Sun, 10 Feb 2019 6:58 pm
-
-
Update address problem with condo
by Sauce95 » Fri, 08 Feb 2019 9:48 pm » in Property Talk, Housing & Rental - 1 Replies
- 1536 Views
-
Last post by sundaymorningstaple
Sat, 09 Feb 2019 7:48 pm
-
Who is online
Users browsing this forum: No registered users and 6 guests