Generating unique ID is excel 2010

lmusser

New Member
Joined
Oct 30, 2010
Messages
7
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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!:biggrin:

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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