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!



[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]STATUS[/TD]
[TD]JOB ID[/TD]
[TD]DATE ASSIGNED[/TD]
[TD]SUPPLIER[/TD]
[TD]HANDLER[/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]200617MS0138[/TD]
[TD]20 June 2017[/TD]
[TD]ABC 01[/TD]
[TD]Marcus[/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617ML0250[/TD]
[TD]28 June 2017[/TD]
[TD]ABC 02[/TD]
[TD]Michael[/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617AA0111[/TD]
[TD]28 June 2017[/TD]
[TD]ABC 03[/TD]
[TD]Angela[/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD]100617ID0138[/TD]
[TD]10 June 2017[/TD]
[TD]ABC 01[/TD]
[TD]Ingrid[/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]050517ML0063[/TD]
[TD]5 June 2017[/TD]
[TD]ABC 04[/TD]
[TD]Michael[/TD]
[/TR]
</tbody>[/TABLE]

 

[Monologue]

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

Hi,

Is there anyway I can upload/attach a test document to show the issue I'm having with this formula? I think it's probably a simple solution (for someone that knows what they're doing) but I can't figure it out.

Thanks!

 

[Monologue]

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

Hi all,

I'm sorry to bump this but has anyone else got any ideas on how to solve my problem? Oldbrewer's formula does not seem to work and I need a solution on this as soon as possible!

Thanks,
M.

 

[oldbrewer]

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

my formula worked on my spreadsheet - put up a google sheets link for us all to look at please

 

[Monologue]

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

Link: https://docs.google.com/spreadsheets/d/1T4MaOxslKhWWlKl0HUFnmlkCTmw6mSiJ7MT7nq40-DM/edit?usp=sharing

 

[oldbrewer]

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

[TABLE="width: 1465"]
<colgroup><col><col><col><col><col><col span="16"></colgroup><tbody>[TR]
[TD]Status[/TD]
[TD]Job no.[/TD]
[TD]Date Requested[/TD]
[TD]Week[/TD]
[TD]Month[/TD]
[TD]Assigned to[/TD]
[TD]Date Assigned[/TD]
[TD]Supplier[/TD]
[TD]Supplier_code[/TD]
[TD]CDE[/TD]
[TD]CountOfCDE[/TD]
[TD]Supp. Ref.[/TD]
[TD]PLU[/TD]
[TD]Barcode[/TD]
[TD]Brand[/TD]
[TD]Description[/TD]
[TD]Deliverables[/TD]
[TD]Dimensions[/TD]
[TD]Design Category[/TD]
[TD]Buyer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD]1/5/17MsGigga[/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD]Date AssignedSupplierBuyer[/TD]
[TD] [/TD]
[TD]H-382[/TD]
[TD="align: right"]123456[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-383[/TD]
[TD="align: right"]123457[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-384[/TD]
[TD="align: right"]123458[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-385[/TD]
[TD="align: right"]123459[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-386[/TD]
[TD="align: right"]123460[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-387[/TD]
[TD="align: right"]123461[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-388[/TD]
[TD="align: right"]123462[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/25/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-389[/TD]
[TD="align: right"]123463[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Barcode[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/18/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-390[/TD]
[TD="align: right"]123464[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Back stamp[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/18/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-391[/TD]
[TD="align: right"]123465[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Back stamp[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/18/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-392[/TD]
[TD="align: right"]123466[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Back stamp[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/18/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-393[/TD]
[TD="align: right"]123467[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Back stamp[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD]Emmet[/TD]
[TD]1/18/2017[/TD]
[TD]Gigga[/TD]
[TD]Gigg[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]H-394[/TD]
[TD="align: right"]123468[/TD]
[TD] [/TD]
[TD]Stabilo[/TD]
[TD]BICYCLE REAR LIGHT[/TD]
[TD]Back stamp[/TD]
[TD]TBC[/TD]
[TD]1: 30min or less[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B3 should be[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]=LEFT(TEXT(G2,"dd-mm-yy"),2)&MID(TEXT(G2,"dd-mm-yy"),4,2)&RIGHT(TEXT(G2,"dd-mm-yy"),2)&LEFT(T2,1)&RIGHT(T2,1)&H2&K2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

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

B2 should be

 

[oldbrewer]

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

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl24, width: 64"]Status[/TD]
[TD="class: xl25, width: 64"]Job no.[/TD]
[TD="class: xl25, width: 64"]Date Requested[/TD]
[TD="class: xl25, width: 64"]Week[/TD]
[TD="class: xl25, width: 64"]Month[/TD]
[TD="class: xl25, width: 64"]Assigned to[/TD]
[TD="class: xl25, width: 64"]Date Assigned[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64"]Completed[/TD]
[TD="class: xl30"]1/5/17[/TD]
[TD="class: xl27, width: 64, align: right"]18/01/2017[/TD]
[TD="class: xl28, width: 64, align: right"]3[/TD]
[TD="class: xl29, width: 64, align: right"]Jan-17[/TD]
[TD="class: xl28, width: 64"]Emmet[/TD]
[TD="class: xl31, width: 64"]1/25/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]considering data in G2 in format m/dd/yyyy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=text(g2,"dd/mm/yyy") =[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/25/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]this proves that G2 is entered as text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]change all your dates to real dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]or you will need complex formulas to detect if month is single or double digit[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Monologue]

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

Hi,

Many thanks for all your help - much appreciated! I was able to get someone else to look at it and this is the formula they produced:

<!--StartFragment-->=TEXT(G2,"DDMMYY")&UPPER(LEFT(Q2,1)&RIGHT(Q2,1))&TEXT(MATCH(H2,H:H,FALSE),"0000")

The formula seems to work fine within my document, without the need to change my date formats. I'm putting it up here, in the case someone else is looking for a similar solution.

Thanks,
M.
<!--EndFragment-->