How to stop duplication in data validation list

[Zubair]

Mr. Excel.xlsx
	A	B	C	D	E	F
1	Entry Form				Data Base
2	Brand	Size			Brand	Size
3	Alpha Kraft	20			Alpha Kraft	20
4	Alpha Kraft	24			Box Board	22
5	Ultra Fluting	26			Delta	24
6	Ultra Fluting	32			Dubai	26
7	Delta	20			Fluting	28
8	Liner	26			Liner	30
9	Special	46			Nazeer Dubai	32
10					Nazeer Kraft	34
11					Nazeer Kraft 160	36
12					Special	38
13					Ultra Fluting	40
14						42
15						44
16						46
17						48
18						50
19
20
21
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A3:A18	List	=$E$3:$E$13

Hi,

A3:A9 is from E3:E13 and B3:B9 is from F3:F18 through data validation list.

No duplicate combination of Brand & Size is acceptable.

So formula required in data validation in column B that for example when user select Brand "Alpha Kraft" in A3 & A4 and size 20 in B3 and B4 it should stop doing this in B4.

Similarly for all Brands by size duplication.

 

[Zubair]

Please help

 

[Zubair]

Any idea how to put stop duplication ?

 

[etaf]

in data validation i have used
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1

that will stop a duplication , however, not sure how that needs to be linked into the database table ?

extend row25 to cover your data range

Various.xlsx
	A	B	C	D	E	F
1	Entry Form				Data Base
2	Brand	Size			Brand	Size
3	Alpha Kraft	20			Alpha Kraft	20
4	Alpha Kraft	24			Box Board	22
5	Ultra Fluting	26			Delta	24
6	Ultra Fluting	32			Dubai	26
7	Delta	20			Fluting	28
8	Liner	26			Liner	30
9	Special	46			Nazeer Dubai	32
10	Alpha Kraft				Nazeer Kraft	34
11	Alpha Kraft				Nazeer Kraft 160	36
12					Special	38
13					Ultra Fluting	40
14						42
15						44
16						46
17						48
18						50
Sheet7

Cells with Data Validation
Cell	Allow	Criteria
B3:B18	Custom	=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1

 

[Zubair]

in data validation i have used
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1

that will stop a duplication , however, not sure how that needs to be linked into the database table ?

extend row25 to cover your data range

Various.xlsx
	A	B	C	D	E	F
1	Entry Form				Data Base
2	Brand	Size			Brand	Size
3	Alpha Kraft	20			Alpha Kraft	20
4	Alpha Kraft	24			Box Board	22
5	Ultra Fluting	26			Delta	24
6	Ultra Fluting	32			Dubai	26
7	Delta	20			Fluting	28
8	Liner	26			Liner	30
9	Special	46			Nazeer Dubai	32
10	Alpha Kraft				Nazeer Kraft	34
11	Alpha Kraft				Nazeer Kraft 160	36
12					Special	38
13					Ultra Fluting	40
14						42
15						44
16						46
17						48
18						50
Sheet7

Cells with Data Validation
Cell	Allow	Criteria
B3:B18	Custom	=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1

Hi etaf - the formula is working perfectly as I needed, but size needs to be selected from the list F3:F18.

I managed to link with database

 

[etaf]

but size needs to be selected from the list F3:F18.

So it does not matter what the brand is the Size MUST match one of the values in F3:F18

=AND(COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1,COUNTIF($F$3:$F$18,B3)>0)

Various.xlsx
	A	B
1	Entry Form
2	Brand	Size
3	Alpha Kraft	20
4	Alpha Kraft	24
5	Ultra Fluting	26
6	Ultra Fluting	32
7	Delta	20
8	Liner	26
9	Special	46
10	Alpha Kraft
11	Nazeer Dubai	46
12	Nazeer Dubai
13
14
15
16
17
18
Dup-datVal

Cells with Data Validation
Cell	Allow	Criteria
B3:B18	Custom	=AND(COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1,COUNTIF($F$3:$F$18,B3)>0)

 

[kvsrinivasamurthy]

So formula required in data validation in column B that for example when user select Brand "Alpha Kraft" in A3 & A4 and size 20 in B3 and B4 it should stop doing this in B4.

Does this means that In B4 entry only 20 is to be stopped. Or not allow any entry.

 

[Zubair]

So it does not matter what the brand is the Size MUST match one of the values in F3:F18

=AND(COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1,COUNTIF($F$3:$F$18,B3)>0)

Various.xlsx
	A	B
1	Entry Form
2	Brand	Size
3	Alpha Kraft	20
4	Alpha Kraft	24
5	Ultra Fluting	26
6	Ultra Fluting	32
7	Delta	20
8	Liner	26
9	Special	46
10	Alpha Kraft
11	Nazeer Dubai	46
12	Nazeer Dubai
13
14
15
16
17
18
Dup-datVal

Cells with Data Validation
Cell	Allow	Criteria
B3:B18	Custom	=AND(COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1,COUNTIF($F$3:$F$18,B3)>0)

Sorry for the unclear questions, let me rephrase my requirement

Background - This is a sales entry form and each brand has sizes from 20 to 50, when we select a brand with a particular size it brings available stock quantity and the user is selecting selling quantity based on available stock.

In case the user wrongly selects the same brand with the same size, a formula will again bring the same available quantity which is wrong, here we wanted to restrict the user not to select duplicate brand/size combinations in the single sales invoice.

Example

Alpha Kraft size 20 selling quantity 200 from available quantity 250
Alpha Kraft size (21 to 50 with available quantities ok, but size 20 again will be treated as a duplication)

Delta size 21 selling quantity 54 ok
Special size 21 selling quantity 65 ok

Special size 22 selling quantity 15 ok
Special size 22 (stop duplication)

Means the same brand again is ok, the same size different brand is ok but the same brand with the same size in single invoice is not ok.

 

[etaf]

Sorry do not understand now

My original post above does what you asked in the below

In case the user wrongly selects the same brand with the same size, a formula will again bring the same available quantity which is wrong, here we wanted to restrict the user not to select duplicate brand/size combinations in the single sales invoice.

I do that with
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1
in Data Validation

I now do not understand your clarification answer example
wheres the quantity in your example - only see size & brand

Means the same brand again is ok, the same size different brand is ok but the same brand with the same size in single invoice is not ok.

So again with
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1
means you could enter
BrandA
with any size , so long as its not duplicated
1,2,3,4,5,6,7, ad infinitum etc etc

you said

the formula is working perfectly as I needed, but size needs to be selected from the list F3:F18.

do you just want a Selection List based on that size
really confused

 

[Zubair]

Sorry do not understand now

My original post above does what you asked in the below


I do that with
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1
in Data Validation

I now do not understand your clarification answer example
wheres the quantity in your example - only see size & brand


So again with
=COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1
means you could enter
BrandA
with any size , so long as its not duplicated
1,2,3,4,5,6,7, ad infinitum etc etc

you said

do you just want a Selection List based on that size
really confused

etaf, my apologies,

The below formula is working perfectly, I have copied/paste this formula incorrectly, extremely sorry for the inconvenience

=AND(COUNTIFS($A$3:$A$25,A3,$B$3:$B$25,B3)=1,COUNTIF($F$3:$F$18,B3)>0)