Avoid Duplicate Entry

[nabeelahmed]

Dear Friends,

Hope alls well. I have a sheet below where i want to avoid from duplication while entering data,, Criteria to find the duplication is Item Tag# with condition of total qty received for example if i already created a PR(Purchased reqisition for a Item 1234 and still not received any quantity against that Tag# then if i will re-enter the same Item tag# to re-order again then i should be notify that i am creating duplicate entry whether it can be with formula or VBA.. Please guide me.. Thanks

Book1
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	01-Sep	1020					BBBB			10	1234						0
3	01-Sep	1020					CCCC			10	1236						0
4	01-Sep	1020					DDDD			20	4561						0
5	01-Sep	1020					EEEE			25	4562						0
6
7
8
9
Sheet1

 

[Toadstool]

Hi NabeelAhmed,

You could use Data Validation to prevent or warn when a duplicate is entered, or you could use Conditional Formatting to highlight duplicates.

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						0
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6											1222
7											1234
8											1233
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=COUNTIF(L:L,L2)>1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L2:L8	Custom	=COUNTIF(L:L,L2)=1

 

[nabeelahmed]

Hi NabeelAhmed,

You could use Data Validation to prevent or warn when a duplicate is entered, or you could use Conditional Formatting to highlight duplicates.

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						0
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6											1222
7											1234
8											1233
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=COUNTIF(L:L,L2)>1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L2:L8	Custom	=COUNTIF(L:L,L2)=1

Dear Toadstool,

Thanks for your response, actually i have a condition too that if previous entered item tag # total qty is more than 0(means if i have received item against that order) then i want to allow the entry for smae item tag number in order to replenish the stock.

 

[Toadstool]

The Conditional Format can be changed. This will highlight only if zero qty has been entered for the same tag, but once any qty has been entered it won't highlight any further duplicates.

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						1
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6											1222
7											1234
8											1233
9											1234
10											1234
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=AND(COUNTIF(L:L,L2)>1,SUMIFS(R:R,L:L,L2)=0)	text	NO

 

[nabeelahmed]

The Conditional Format can be changed. This will highlight only if zero qty has been entered for the same tag, but once any qty has been entered it won't highlight any further duplicates.

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						1
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6											1222
7											1234
8											1233
9											1234
10											1234
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=AND(COUNTIF(L:L,L2)>1,SUMIFS(R:R,L:L,L2)=0)	text	NO

Dear Thanks for your response but its not helpful for me as i have to repeat the item number , i want to be notified only for the reason if i had requested for the same item number before and that specific item received or not...

Regards

 

[Toadstool]

I'm sorry but I don't understand exactly what you're trying to do.

 

[nabeelahmed]

I'm sorry but I don't understand exactly what you're trying to do.
[/
Its ok.. Actually i am purchasing different products.. what i want.. suppose there is an item "Gloves" with item code 3961 and the quantity i ordered is 10. Which is not delivered and still pending and i will again raise next order for the same product which is still pending i want to be notified that i am creating duplicate entry and if the previous ordered quantity has been received and nothing is pending then nothing should be highlighted and i could raise new order.. this is what i want whenever i will raise new order for any item and if there is already an order which is yet to be delivered then i want notification/Msg. Hope you will understand

 

[Toadstool]

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						9
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6	1-Sep-20	1020					EEEE			12	1234
Sheet1 (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=AND(L2<>"",COUNTIF(L:L,L2)>1,(SUMIFS(R:R,L:L,L2)<SUMIFS(K:K,L:L,L2)-VALUE(K2)))	text	NO

 

[nabeelahmed]

NabeelAhmed.xlsx
	B	C					H			K	L						R
1	Date	PR#					Item Description			Re-Order Qty	Item Tag#						Total Qty Received
2	1-Sep-20	1020					BBBB			10	1234						9
3	1-Sep-20	1020					CCCC			10	1236						0
4	1-Sep-20	1020					DDDD			20	4561						0
5	1-Sep-20	1020					EEEE			25	4562						0
6	1-Sep-20	1020					EEEE			12	1234
Sheet1 (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L15	Expression	=AND(L2<>"",COUNTIF(L:L,L2)>1,(SUMIFS(R:R,L:L,L2)<SUMIFS(K:K,L:L,L2)-VALUE(K2)))	text	NO

Dear Thank you So much for your support.. that perfect ...