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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,921
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 30, 2010
Messages
7
Just for kicks...what would you do if the data is not always sorted?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,921
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

TEMiranda

New Member
Joined
Mar 6, 2014
Messages
2

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,921
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 6, 2014
Messages
2
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,921
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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
Top