Generating unique ID is excel 2010

[lmusser]

I have a spreadsheet with the following layout

<TABLE style="WIDTH: 402pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=536><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 177pt; mso-width-source: userset; mso-width-alt: 8630" width=236><TBODY><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 84pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=26 width=112>ID No.</TD><TD style="BORDER-BOTTOM: #a7bfde 1.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; FONT-FAMILY: Calibri; COLOR: #1f497d; FONT-SIZE: 13pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl66 width=85>Column1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; FONT-FAMILY: Calibri; COLOR: #1f497d; FONT-SIZE: 13pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 width=103>Last Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 177pt; FONT-FAMILY: Calibri; COLOR: #1f497d; FONT-SIZE: 13pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 width=236>First Names</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>AdamsChristopher</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Christopher</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">AdamsDebora & Sammy</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Debora & Sammy</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>AdamsDebora & Sammy</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Debora & Sammy</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>AdamsDebora & Sammy</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Debora & Sammy</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>AdamsJeffrey</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d9d9d9; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D9D9D9 none" class=xl64>Jeffrey</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=24></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>AdamsJeffrey</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Adams</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 12pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64>Jeffrey</TD></TR></TBODY></TABLE>
I would like a way to assign an ID number to each unique row. As you can see in this example there would be three unique IDs. I just can't figure out how to do this. Any help would be appreciated. Thanks

 

[Joe4]

Assuming your data is always sorted, jet's say that your first ID number cell is A2.
Then you can enter a "1" in A2, and enter this formula in cell A3 and copy down for all rows.

=IF(B3=B2,A2,A2+1)

So every time it comes to a new entries appears in column B, it will increment the ID in column A by 1.

 

[lmusser]

Thanks so much...this worked perfectly!

 

[lmusser]

Just for kicks...what would you do if the data is not always sorted?

 

[Joe4]

Just for kicks...what would you do if the data is not always sorted?

Quite frankly, I would sort it myself before applying the formula!

I am sure it is possible to program something to do it, but it would probably be much more complex, and not worth the effort if one can simply just sort the data first.

 

[lmusser]

I see what you mean. Thanks again for your help!

 

[TEMiranda]

How about basing on 2 columns? I invented a formula like this based on the formula you've given, I've declared an ID beginning with 10001 on A2, so it will add incrementally, right? This is my formula:
=if(B3,C3=B2,C2,A2,A2+1)
it wont take effect.

 

[Joe4]

Your IF statement is not constructed properly. There are only three arguments in an IF statement:
=IF(condition to check, what to return if condition is true, what to return if condition is false)

Unless B3 is True or False, you have not written your condition correctly, and you have 5 arguments instead of 3.

 

[TEMiranda]

Hi Joe,

I have a spreadsheet that is similar with the 1st image given, do i have to concatenate it then base on the concatenated result to have a Unique ID? Or I have to generate a formula? I need to base on the "LastName(in column B)" and "FirstName(in column C)" columns, if possible, also in "MiddleName(in column D)" column, then UDI in column A. Would you mind if I ask what formula should I use? Because I want to generate UID (Unique ID), lets begin with UID 10001. And this will be the logic, "If B3, C3, D3 ≠ B2, C2, D3, then A2+1"

Can I use IF Statement in this? Thanks dude!

 

[Joe4]

I think this is what you want:
=if(AND(B3=B2,C3=C2),A2,A2+1)
or this:
=if(B3&C3=B2&C2,A2,A2+1)

Either one should work.