Formula that will generate unique ID based on text data in multiple columns

[Monologue]

Hi all,



We have a document in work that is used to record design tasks. Each new job can have multiple lines in the document but should all share one unique ID. The document contains about 20 columns but only the below 5 will impact the data that should create the Job ID. Currently, the 'JOB ID' column uses a unique number created by another department. We want to get away from that and use our own number, for numerous reasons. The current system does not take in to account any of the below data, which is what we want to do with the new system. The job id should follow the following structure: 'DATE ASSIGNED' + 'BUYER' + 'SUPPLIER', where 'HANDLER' and 'SUPPLIER' are represented by a code that should be located in a second sheet called 'Lists'. The code for the handler should take the first and last letter of the name and the supplier code should simply be a sequential number based on the supplier's position in the column on the second sheet 'Lists'. So in the first example, the '0138' at the end simply refers the fact that this particular supplier is number 138 in the list. Is what I have described here possible and have you any ideas as to what the formula would look like? I have a column next to the 'SUPPLIER' column in the 'Lists' sheet that contains the sequential numbers. Many thanks for reading and if you need any further explanations / information, please let me know!

STATUS	JOB ID	DATE ASSIGNED	SUPPLIER	HANDLER
Assigned	200617MS0138	20 June 2017	ABC 01	Marcus
In Progress	280617ML0250	28 June 2017	ABC 02	Michael
In Progress	280617AA0111	28 June 2017	ABC 03	Angela
Completed	100617ID0138	10 June 2017	ABC 01	Ingrid
Assigned	050517ML0063	5 June 2017	ABC 04	Michael

<tbody>
</tbody>

 

[oldbrewer]

Re: Help with formula that will generate unique ID based on text data in multiple columns

STATUS

JOB ID

DATE ASSIGNED

SUPPLIER

HANDLER

Assigned

200617Msgn

20-06-17

green

Marcus

In Progress

280617Mlrd

28-06-17

red

Michael

In Progress

280617Aabe

28-06-17

blue

Angela

Completed

100617Idgn

10-06-17

green

Ingrid

Assigned

050617Mlpk

05-06-17

pink

Michael

B2

=LEFT(TEXT(C2,"dd-mm-yy"),2)&MID(TEXT(C2,"dd-mm-yy"),4,2)&RIGHT(TEXT(C2,"mm-dd-yy"),2)&LEFT(E2,1)&RIGHT(E2,1)&LEFT(D2,1)&RIGHT(D2,1)

not sure why you want a serial number as well

would Marcus have another green job on the same date ?

<colgroup><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[Monologue]

Re: Help with formula that will generate unique ID based on text data in multiple columns

Thanks Oldbrewer. For the Supplier name, we want to use the full number, rather than the 1st and 2nd letter/digit as per your formula. Knowing this - how would that change the formula?

Thanks!

 

[oldbrewer]

Re: Help with formula that will generate unique ID based on text data in multiple columns

STATUS	JOB ID	DATE ASSIGNED	SUPPLIER	HANDLER
Assigned	200617Msgreen	20-06-17	green	Marcus
In Progress	280617Mlred	28-06-17	red	Michael
In Progress	280617Aablue	28-06-17	blue	Angela
Completed	100617Idgreen	10-06-17	green	Ingrid
Assigned	050617Mlpink	05-06-17	pink	Michael
	B2
	=LEFT(TEXT(C2,"dd-mm-yy"),2)&MID(TEXT(C2,"dd-mm-yy"),4,2)&RIGHT(TEXT(C2,"mm-dd-yy"),2)&LEFT(E2,1)&RIGHT(E2,1)&D2
	not sure why you want a serial number as well
	would Marcus have another green job on the same date ?

<colgroup><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[Monologue]

Re: Help with formula that will generate unique ID based on text data in multiple columns

	not sure why you want a serial number as well
	would Marcus have another green job on the same date ?

<tbody>
</tbody>

Hi Oldbrewer,

I'm not sure what you mean with "why you want a serial number as well"? The handlers and suppliers all cross over so yes, it's very likely that Marcus and Ingrid may be working on two different orders with the same supplier, on the same day. And it's also possible for Marcus to open a job with green on the same day. In this case, because I control the 'Assigned Date' I would simply choose a different day to assign the job. I will give this code a try and let you know how I get on.

Thanks again!

 

[oldbrewer]

Re: Help with formula that will generate unique ID based on text data in multiple columns

STATUS	JOB ID	DATE ASSIGNED	SUPPLIER	HANDLER	CDE	COUNTOFCDE
Assigned	200617Msgreen1	20-06-17	green	Marcus	20-06-17greenMarcus	1
In Progress	280617Mlred1	28-06-17	red	Michael	28-06-17redMichael	1
In Progress	280617Aablue1	28-06-17	blue	Angela	28-06-17blueAngela	1
Completed	100617Idgreen1	10-06-17	green	Ingrid	10-06-17greenIngrid	1
Assigned	050617Mlpink1	05-06-17	pink	Michael	05-06-17pinkMichael	1
Assigned	200617Msgreen2	20-06-17	green	Marcus	20-06-17greenMarcus	2
	B2
	=LEFT(TEXT(C2,"dd-mm-yy"),2)&MID(TEXT(C2,"dd-mm-yy"),4,2)&RIGHT(TEXT(C2,"mm-dd-yy"),2)&LEFT(E2,1)&RIGHT(E2,1)&D2&G2
	if you can tolerate helper columns F and G you now have a unique ID generator
	note I added the last row identical to the first
	CDE is a concatenation
	=TEXT(C2,"dd-mm-yy")&D2&E2
	note date converted to text

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Monologue]

Re: Help with formula that will generate unique ID based on text data in multiple columns

	if you can tolerate helper columns F and G you now have a unique ID generator
	note I added the last row identical to the first
	CDE is a concatenation
	=TEXT(C2,"dd-mm-yy")&D2&E2
	note date converted to text

<tbody>
</tbody>

Can I place columns F and G in a separate sheet within the workbook or do they have to be in the same sheet?

 

[Monologue]

Re: Help with formula that will generate unique ID based on text data in multiple columns

Quick follow on question - in this document, as explained, I have two sheets: 'Projects' and 'Lists'. The 'lists' sheet is used for lists that are referenced in pop-down lists in 'Projects' (which is the main sheet that's used). In the lists sheet, I have a column with all suppliers, listed (in no order) by name. I would like to add a column next to this, with a short numerical code, generated from the name in the supplier column. For example, supplier "Balloons Inc." becomes '0123'. This code, 0123, is then used to make up the final job id, instead of the full supplier name. So what I want to do is create unique numbers/codes from the names of the suppliers (it can be as simple as sequential, doesn't matter as long as it's unique), and reference these numbers in the 'Projects' sheet and use this number/code to form part of the final job id. I hope I have explained myself properly, but feel free to let me know if I haven't!

Thanks.

 

[Monologue]

Re: Help with formula that will generate unique ID based on text data in multiple columns

Hi Oldbrewer,

I am getting a #VALUE! error code with the formula(s) above. Any ideas? I've typed your formula to the letter, except for when the letter of the column was different from yours above, but that wouldn't have any bearing on the final result I would have thought? Some of the columns do use in-cell drop downs with data from the second sheet, 'Lists'. For example, instead of the Supplier column in the example above I have a column next to it with the following formula: =LEFT(H2, 4) which converts the full supplier name to a 'code' of the first 4 letters. So, is using this data giving me the error?

Thanks!

 

[oldbrewer]

Re: Help with formula that will generate unique ID based on text data in multiple columns

it shouldn't as in the case of green it will be gree

to reference a cell in sheet lists the format is =lists!A1