Next available number in column

[Ana_P]

Hi,

I have an excel s/sheet with order numbers in one column that I would like to prefill based on the next available number in that column.

A few things to note:
- the numbering is a 5 digit number with the first 2 digits being the year the order is in (i.e. 22) followed by 001, 002 etc
- if an order contains multiple items it will use the same order number therefore the next available number will be overridden to accommodate this
- I need the formula to keep working in subsequent rows even if the previous row was overridden per above point
- the data set is in a table
- the numbers aren't in order in the table as the table is sorted by different columns at different times

Any help would be greatly appreciated.

Thanks

 

[Ana_P]

If you want the numbers to remain the same even when the data is sorted differently then that may not be possible with a formula approach. However, that may depend on just what information that you have in your table. Would need to see some of that - all columns- (with any sensitive data disguised) to help decide.

Yes, the number must not change if the data is sorted differently. You will notice some numbers are missing in the sequence - this is because an order was placed then cancelled at some point and in that case the row/s are deleted.

So, to reiterate, the first order of each new year will start with year followed by 000, 001 etc. Multiple items per order require the same order number (but entered on different rows in Excel), and deleted orders will result in missing numbers, but I'm assuming that's not an issue. I require the next available number to prefill when entering a new order, with the possibility of overriding that number if the customer orders multiple items in one order on the same date.

Hope that makes sense.

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date Raised	Date Delivered	F/Y	CWO	Finisher	Add LT	Customer	Customer PO	Comments	Due Date	Revised ETA	Schedule Date	Schedule Week	Date Received
93	18/01/22	18/01/22	F	22000	AB		GHI			1/02/22		1/02/22	5	25/01/22
94	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
95	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
96	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
97	18/01/22	18/01/22	F	22004	AB		PQR			1/02/22		1/02/22	5	3/02/22
98	18/01/22	18/01/22	F	22005	AB		AZ			1/02/22		1/02/22	5	16/02/22
99	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
100	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
101	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
102	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
103	18/01/22	18/01/22	F	22009	AB		ABC			1/02/22		1/02/22	5	1/02/22
104	18/01/22	18/01/22	F	22009	AB		ABC			1/02/22		1/02/22	5	1/02/22
105	19/01/22	19/01/22	F	22011	AB		OP8			2/02/22		2/02/22	5	11/02/22
106	20/01/22	24/01/22	F	22012	AB		JKL			7/02/22		7/02/22	6	17/02/22
107	20/01/22	24/01/22	F	22012	AB		JKL			7/02/22		7/02/22	6	17/02/22
108	20/01/22	31/01/22	F	22012	AB		JKL			14/02/22		14/02/22	7	25/02/22
109	29/03/22	29/03/22	F	22012	AB		JKL			12/04/22		12/04/22	15	14/04/22
110	20/01/22	2/02/22	F	22013	AB		JKL			16/02/22		16/02/22	7	25/02/22
111	20/01/22	21/01/22	F	22014	AB		JKL			4/02/22		4/02/22	5	30/05/22
112	20/01/22	29/03/22	F	22014	AB		JKL			12/04/22		12/04/22	15	12/04/22
113	20/01/22	19/01/22	F	22015	AB		JKL			2/02/22		2/02/22	5	10/02/22
114	20/01/22	31/01/22	F	22016	AB		YZ			14/02/22		14/02/22	7	4/02/22
115	20/01/22	31/01/22	F	22016	AB		YZ			14/02/22		14/02/22	7	17/02/22
116	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	28/01/22
117	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	28/01/22
118	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	1/02/22
119	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	14/02/22
120	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	14/02/22
121	25/01/22	25/01/22	F	22019	AB		AZ			8/02/22		8/02/22	6	16/02/22
122	25/01/22	25/01/22	F	22020	AB		PQR			8/02/22		8/02/22	6	3/02/22
123	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
124	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
125	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
126	25/01/22	25/01/22	B	22022	CD		BY			22/03/22		22/03/22	12	17/02/22
July-Dec 2022

Cell Formulas
Range	Formula
L93:L126	L93	=IF(ISBLANK([@[Revised ETA]]),[@[Due Date]],[@[Revised ETA]])
M93:M126	M93	=IF(OR([@[Schedule Date]]="",[@[Schedule Date]]="Missing Info"),"",ISOWEEKNUM([@[Schedule Date]]))
J93:J126	J93	=IF(ISBLANK([@[Date Raised]]),"",IF(OR(ISBLANK([@[Date Delivered]]),ISBLANK([@[F/Y]])),"Missing Info",[@[Date Delivered]]+VLOOKUP([@[F/Y]],Lead_Time,3,FALSE)+[@[Add LT]]))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("ASAP",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Urgent",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Repro",$I2)))	text	NO
L2:M601	Expression	=AND($Q2="Open",$M2<Current_Week)	text	NO
J2:J601	Expression	=ISNUMBER($K2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J2:L126	Date	>1/01/1900
N2:N126	Date	>1/01/1900
A2:B601	Date	>1/01/1900
C2:C601	List	=Reference!$A$3:$A$7
E2:E601	List	=Reference!$A$15:$A$17
F2:F601	Whole number	between 0 and 99

 

[Peter_SSs]

Yes, the number must not change if the data is sorted differently. You will notice some numbers are missing in the sequence - this is because an order was placed then cancelled at some point and in that case the row/s are deleted.

Ok, since you mentioned deleting rows then what I thought may have been feasible by formula in fact isn't.
I think that you would need to

1. Use macros for this task.
2. Keep a permanent log somewhere of order numbers used. After all, if the most recent order number is 22345 and then that order is cancelled & the row(s) deleted, I presume you would want the next order number to be 22346. Without that permanent record there would be no way of knowing that 22345 had already been used (& deleted) and is therefore not available for this next order.

 

[Ana_P]

Ok, since you mentioned deleting rows then what I thought may have been feasible by formula in fact isn't.
I think that you would need to

1. Use macros for this task.
2. Keep a permanent log somewhere of order numbers used. After all, if the most recent order number is 22345 and then that order is cancelled & the row(s) deleted, I presume you would want the next order number to be 22346. Without that permanent record there would be no way of knowing that 22345 had already been used (& deleted) and is therefore not available for this next order.

There would, more likely than not, be another order added before one is deleted. And even if it did happen the way you suggested, then there would be no need for 22345 to not be used, i.e it could be available for the next order.

 

[Peter_SSs]

Does that mean that any order number can be re-used if it gets deleted?
Example 22001, 22002 and 22003 are used and then 22002 gets deleted can it get used again?

 

[Ana_P]

No, the next number would always be the next available number after the largest number used in the list.

 

[Peter_SSs]

1. Well, I think that you are going to need a macro to produce the CWO numbers. Is that acceptable?

2. Is it safe to assume that a customer will not have two different orders raised on the same date?

 

[Ana_P]

1. Well, I think that you are going to need a macro to produce the CWO numbers. Is that acceptable?

2. Is it safe to assume that a customer will not have two different orders raised on the same date?

1. I'd prefer to use a formula over a macro.

2. Yes, I'd say so.

I have managed to come up with the following array formula to achieve what I initially wanted:

=TEXT([@[Date Raised]],"YY")&TEXT(MIN(IF(ISERROR(MATCH(TEXT([@[Date Raised]],"YY")&TEXT(ROW($1:$9999),"000"),D$2:D601,0)),ROW($1:$9999),10000)),"000")

However, I have just been advised that old "completed" orders will be deleted from the sheet and therefore I need the next available number after the largest one used (rather than the next available "unused" number. Any chance you can help me edit my formula to meet this new requirement?

Thanks

 

[Peter_SSs]

I have managed to come up with the following array formula to achieve what I initially wanted:

Can you post a small sample table with XL2BB with that formula in use?

 

[Ana_P]

Can you post a small sample table with XL2BB with that formula in use?

I have deleted XL2BB from my computer as it was causing Excel to freeze every time I saved my document and the only way I could work around it was to push the power button to turn of my laptop.

I copied the CWO column to another sheet and then entered the formula and made the column a calculated column, then copied the initial CWO values back into the worksheet and made sure they were formatted as text, and those values are the exception to the calculated column.

 

[Peter_SSs]

I copied the CWO column to another sheet and then entered the formula and made the column a calculated column, then copied the initial CWO values back into the worksheet and made sure they were formatted as text, and those values are the exception to the calculated column.

I'm afraid that I can't reliably replicate that.