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

M

Monologue

New Member
Joined
Jun 28, 2017
Messages
10
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]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Re: Help with formula that will generate unique ID based on text data in multiple columns

[TABLE="width: 1106"]
<colgroup><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]STATUS[/TD]
[TD]JOB ID[/TD]
[TD]DATE ASSIGNED[/TD]
[TD]SUPPLIER[/TD]
[TD="colspan: 2"]HANDLER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]200617Msgn[/TD]
[TD="align: right"]20-06-17[/TD]
[TD]green[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Mlrd[/TD]
[TD="align: right"]28-06-17[/TD]
[TD]red[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Aabe[/TD]
[TD="align: right"]28-06-17[/TD]
[TD]blue[/TD]
[TD]Angela[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD]100617Idgn[/TD]
[TD="align: right"]10-06-17[/TD]
[TD]green[/TD]
[TD]Ingrid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]050617Mlpk[/TD]
[TD="align: right"]05-06-17[/TD]
[TD]pink[/TD]
[TD]Michael[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD]B2[/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: 11"]=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)[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]not sure why you want a serial number as well[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]would Marcus have another green job on the same date ?[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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!
 
Upvote 0
Re: Help with formula that will generate unique ID based on text data in multiple columns

[TABLE="width: 978"]
<colgroup><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]STATUS[/TD]
[TD]JOB ID[/TD]
[TD]DATE ASSIGNED[/TD]
[TD]SUPPLIER[/TD]
[TD="colspan: 2"]HANDLER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]200617Msgreen[/TD]
[TD="align: right"]20-06-17[/TD]
[TD]green[/TD]
[TD]Marcus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Mlred[/TD]
[TD="align: right"]28-06-17[/TD]
[TD]red[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Aablue[/TD]
[TD="align: right"]28-06-17[/TD]
[TD]blue[/TD]
[TD]Angela[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD]100617Idgreen[/TD]
[TD="align: right"]10-06-17[/TD]
[TD]green[/TD]
[TD]Ingrid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]050617Mlpink[/TD]
[TD="align: right"]05-06-17[/TD]
[TD]pink[/TD]
[TD]Michael[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD]B2[/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: 9"]=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[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]not sure why you want a serial number as well[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]would Marcus have another green job on the same date ?[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Help with formula that will generate unique ID based on text data in multiple columns

oldbrewer said:
[TABLE="width: 978"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]not sure why you want a serial number as well[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]would Marcus have another green job on the same date ?[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]
Click to expand...

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!
 
Upvote 0
Re: Help with formula that will generate unique ID based on text data in multiple columns

[TABLE="width: 960"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]STATUS[/TD]
[TD]JOB ID[/TD]
[TD]DATE ASSIGNED[/TD]
[TD]SUPPLIER[/TD]
[TD]HANDLER[/TD]
[TD]CDE[/TD]
[TD]COUNTOFCDE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]200617Msgreen1[/TD]
[TD]20-06-17[/TD]
[TD]green[/TD]
[TD]Marcus[/TD]
[TD]20-06-17greenMarcus[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Mlred1[/TD]
[TD]28-06-17[/TD]
[TD]red[/TD]
[TD]Michael[/TD]
[TD]28-06-17redMichael[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Progress[/TD]
[TD]280617Aablue1[/TD]
[TD]28-06-17[/TD]
[TD]blue[/TD]
[TD]Angela[/TD]
[TD]28-06-17blueAngela[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Completed[/TD]
[TD]100617Idgreen1[/TD]
[TD]10-06-17[/TD]
[TD]green[/TD]
[TD]Ingrid[/TD]
[TD]10-06-17greenIngrid[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]050617Mlpink1[/TD]
[TD]05-06-17[/TD]
[TD]pink[/TD]
[TD]Michael[/TD]
[TD]05-06-17pinkMichael[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assigned[/TD]
[TD]200617Msgreen2[/TD]
[TD]20-06-17[/TD]
[TD]green[/TD]
[TD]Marcus[/TD]
[TD]20-06-17greenMarcus[/TD]
[TD]2[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]=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[/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]if you can tolerate helper columns F and G you now have a unique ID generator[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]note I added the last row identical to the first[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CDE is a concatenation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=TEXT(C2,"dd-mm-yy")&D2&E2[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]note date converted to text[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Help with formula that will generate unique ID based on text data in multiple columns

oldbrewer said:
[TABLE="width: 960"]
<tbody>[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]if you can tolerate helper columns F and G you now have a unique ID generator[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]note I added the last row identical to the first[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CDE is a concatenation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=TEXT(C2,"dd-mm-yy")&D2&E2[/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]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]note date converted to text[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Can I place columns F and G in a separate sheet within the workbook or do they have to be in the same sheet?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Dataset question - listing multiple lines of data with the same ID by group
Replies
1
Views
394
alansidman
alansidman
D
  • Question Question
VBA Form to enter data to table and create a unique nonrepeating id number based on date
Replies
3
Views
2K
HighAndWilder
H
Z
  • Locked
  • Question Question
Extract 1-N value(s) from a string of text in 1 cell - multiple extraction values in separate tables (excel formula needed)
Replies
1
Views
1K
Fluff
Fluff
S
  • Solved
VBA code to transpose rows to columns and copy data
Replies
4
Views
609
Mario_R
M
S
  • Question Question
Copy only those rows that have green highlighted cells on random columns
Replies
0
Views
501
Sunnyji
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top