Auto assign number with +1 If meet the condition given

[bobby786]

Hi all experts ,

i have workbook in which i need to maintain the orders and producer of the product.

My Goal:
COL C Producer ( if its new product and not enter before in the above COL , then it should auto assign Invoice number in D6,D7,D8 as "ORD#1" , if its 2nd time then "ORD#2" , 3rd time "ORD#3" )
For Example :
Product1 = Ord#1
Product2= Ord#1
Prodcut1= Ord#2
Product2=Ord#2
Product3=Ord#1

purpose is to track that particular order with invoice , as same product ordered in different times will carry different details in the other area of the sheet so i need to assign a unique number to be able to identify them in later stages . If i explain it poorly please do let me know i will try my best again.

PS: English is not my native language , but i use it everyday

BOOK_2020 - 2 - Copy.xlsm
	C	D	E	F
6	JF Sonic	ORD#1	500	A800
7	JF Sonic	ORD#2	1000	A800
8	Longzhen	ORD#1	5000	S30
9	Longzhen	ORD#2	4000	S30
10	JF Sonic	ORD#3	1000	A800
11	JF Sonic	ORD#4	1000	A800
12	Guiyang CB	ORD#1	500	A800
13	Longzhen	1	500	A800
CENTRAL

Cells with Data Validation
Cell	Allow	Criteria
C6:F13	Any value

 

[Fluff]

How about

+Fluff New.xlsm
	C	D	E	F
6	JF Sonic	ORD#1	500	A800
7	JF Sonic	ORD#2	1000	A800
8	Longzhen	ORD#1	5000	S30
9	Longzhen	ORD#2	4000	S30
10	JF Sonic	ORD#3	1000	A800
11	JF Sonic	ORD#4	1000	A800
12	Guiyang CB	ORD#1	500	A800
13	Longzhen	ORD#3	500	A800
Main

Cell Formulas
Range	Formula
D6:D13	D6	="ORD#"&COUNTIFS(C$6:C6,C6)

 

[bobby786]

How about

+Fluff New.xlsm
	C	D	E	F
6	JF Sonic	ORD#1	500	A800
7	JF Sonic	ORD#2	1000	A800
8	Longzhen	ORD#1	5000	S30
9	Longzhen	ORD#2	4000	S30
10	JF Sonic	ORD#3	1000	A800
11	JF Sonic	ORD#4	1000	A800
12	Guiyang CB	ORD#1	500	A800
13	Longzhen	ORD#3	500	A800
Main

Cell Formulas
Range	Formula
D6:D13	D6	="ORD#"&COUNTIFS(C$6:C6,C6)

@Fluff
Great Mate , insted of C6 i applied on F6 to make it product based count.

Thank you again.

Regards

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bobby786]

@Fluff and all others expert out there.

Its been a long for this post , i was not able to work on it , due to my health conditions.
I required a help from you. The above provided formula works fine if there is no manually define ORD# in COL D , but since we have some product with history of product therefore i need to assign its current ORD# , which i try to do it manually as it can be seen in COL D8 , but when i try to make new instance of the same product instead it should give me ORD#8 it just give me ORD#2 ,

Is it possible to re-arrange the formula in a way that it should check the ORD#7 of that particular product and add+1 from that number.
if i explain it not correctly please forgive me , i am not native english guy.

Thanks in advance.

BOOK_2020.V3_2020-10-03.xlsm
	C	D	E	F	G
6	Arrow	ORD#1	10000	Solo X5	2019-12-04
7	Arrow	ORD#2	10,000	Solo X5	2019-12-04
8	Amaway	ORD#7		Solo X9	2021-03-30
9	Amaway	ORD#2		Solo X9
CENTRAL

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	C	D	E	F	G
6	Arrow	ORD#1	10000	Solo X5	43803
7	Arrow	ORD#2	10000	Solo X5	43803
8	Amaway	ORD#7		Solo X9	44285
9	Amaway	ORD#8		Solo X9
10		ORD#9		Solo X9
Results

Cell Formulas
Range	Formula
D6:D7,D9:D10	D6	=IF(F6="","",IF(COUNTIFS(F$6:F6,F6)=1,"ORD#1","ORD#"&MAX(IF(F3:F5=F6,REPLACE(D3:D5,1,4,"")+0))+1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bobby786]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	C	D	E	F	G
6	Arrow	ORD#1	10000	Solo X5	43803
7	Arrow	ORD#2	10000	Solo X5	43803
8	Amaway	ORD#7		Solo X9	44285
9	Amaway	ORD#8		Solo X9
10		ORD#9		Solo X9
Results

Cell Formulas
Range	Formula
D6:D7,D9:D10	D6	=IF(F6="","",IF(COUNTIFS(F$6:F6,F6)=1,"ORD#1","ORD#"&MAX(IF(F3:F5=F6,REPLACE(D3:D5,1,4,"")+0))+1))
Press CTRL+SHIFT+ENTER to enter array formulas.

@Fluff You are Super Formula Hero . Its working fine. Thank you man.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bobby786]

Hi All,

its been long time,I am using the above formula provided by Great @ Fluff with no problems.But recently I am getting a strange error.For example cell D122 showing ORD#4 which is correct.But when Double click it and press Enter it will show as #Value error. As can be seen in D133.
I try to figure it out myself,but after failing I am seeking the help here again.

BOOK_2020.V3_2021-04-11 - Clean.Code_Master_Updated_Untouched.xlsm
	C	D
121	Fuxi Technology	ORD#3
122	Fuxi Technology	ORD#4
123	Huwanda	#VALUE!
CENTRAL

Cell Formulas
Range	Formula
D121:D122	D121	=IF(F121="","",IF(COUNTIFS(F$6:F121,F121)=1,"ORD#1","ORD#"&MAX(IF(F$6:F120=F121,REPLACE(D$6:D120,1,4,"")+0))+1))
D123	D123	=IF(F123="","",IF(COUNTIFS(F$6:F123,F123)=1,"ORD#1","ORD#"&MAX(IF(F$6:F122=F123,REPLACE(D$6:D122,1,4,"")+0))+1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D123	Expression	=$DT123	text	NO
D122	Expression	=$DT122	text	NO
D121	Expression	=$DT121	text	NO
AG18:AY18,AC18:AE18,AC21:AE21,AC23:AE36,AC40:AE51,AC53:AE60,AC15:AE16,AG15:AY16,AG21:AY21,AG43:AT51,B6:AY6,AF6:AF72,AC17:AY17,AC52:AT52,AC37:AY39,AC22:AY22,AC19:AY20,AG23:AY36,Y7:AA73,AC61:AT73,AS6:AS73,C75,E75:W75,AG53:AT60,AU43:AY73,AG40:AY42,C76:W76	Expression	=$DT6	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C121:D123	Any value

 

[Fluff]

You are getting the error in D123 as that formula is no longer array entered. You need to go back into the cell & confirm the formula with Ctrl Shift Enter.