# Unique Sequential Numbers



## Andy42k (Jan 4, 2023)

Hi all. I am trying to create a spreadsheet to keep track of different units that submit requests to my unit throughout the year with a unique sequential number. The units are designated by the numbers 1-22. I would like the sequential number to read as follows: 15 - 1 -23...the number 15 would the number of the unit making the request, the number 1 would the number request for the year, and 23 would the year. Any direction with this would be greatly appreciated. TIA


----------



## etaf (Jan 4, 2023)

how is the unit determined , is this entered on the sheet
otherwise you could put
'15-1-23 into cell A2
and then
="15-"&MID(A2,4,1)+1&"-"&TEXT(TODAY(),"yy")
copied down

perhaps with an IF - so a cell has to be completed in that row for it to show up

Or you could use ROW() 
="15-"&ROW()-1&"-"&TEXT(TODAY(),"yy")

Book1AB1215-1-2315-1-23315-2-2315-2-23415-3-2315-3-23515-4-2315-4-23615-5-2315-5-23715-6-2315-6-23815-7-2315-7-23915-8-2315-8-231015-9-2315-9-231115-10-2315-10-231215-2-2315-11-231315-3-2315-12-231415-13-23Sheet1Cell FormulasRangeFormulaA3:A13A3="15-"&MID(A2,4,1)+1&"-"&TEXT(TODAY(),"yy")B2:B14B2="15-"&ROW()-1&"-"&TEXT(TODAY(),"yy")


----------



## Andy42k (Jan 6, 2023)

Thank you for your response. The number of the unit will be inputted on the sheet.


----------



## etaf (Jan 6, 2023)

then you could just use concatenate to add the unit number 
but presumably you would want 

15-1-23
15-2-23
18-1-23
15-3-23
18-2-23



and not
15-1-23
15-2-23
18-3-23
15-4-23
18-5-23

maybe an example with expected results would help 
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.








						XL2BB - Excel Range to BBCode
					

Excel 'mini-sheet' in messages - XL2BB  Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...




					www.mrexcel.com
				




You can also test to see if it works ok, in the "Test Here" forum. 








						Test Here
					

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc.  Threads in this forum are automatically deleted after no replies for seven (7) days




					www.mrexcel.com
				




OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed


----------



## Andy42k (Jan 6, 2023)

The unit numbers would be picked from a list in the cell. I need a number sequential number generated a particular number is picked from row 1.


----------



## etaf (Today at 7:51 AM)

sorry for the delay, i was getting into all sorts of very complicated formulas using max and search 

BUT a count may do

how about 

Book30ABCDEFG1unitID211-1-23333-1-23455-1-23511-2-23633-2-23766-1-23811-3-23933-3-231055-2-231111-4-231233-4-231366-2-23Sheet1Cell FormulasRangeFormulaG2:G13G2=A2&"-"&COUNTIF($A$2:A2,A2)&"-"&TEXT(TODAY(),"yy")


----------

