Serial Number

charlie_3dc

New Member
Joined
Jan 28, 2015
Messages
5
Hi there,

I want to track an individual across a number of years. I have their name eg: Smith, John and I want to track this across different years databases and find how this person's qualifications changed across years. I was thinking of assigning a serial number to an individual but need that serial number to remain consistent across years and only become unique when a new person is entered?

Can anyone help?

For example:
2013 - Cahill, Tim - 000001
2013 - Jedinak, Mile - 000002

2014 - Cahill, Tim - 000001
2014 - Jedinak, Mile - 000002
2014 - Kruse, Robbie - 000003 (assign new serial as new person)

2015 - Jedinak, Mile - 000002 (do not start again at 000001 when that person is no longer)
2015 - Kruse, Robbie - 000003
2015 - Ryan, Matt - 000004

Thanks,

Charlie
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

dya

New Member
Joined
Apr 26, 2011
Messages
49
Does it all need to be 1 column? You won't be entering the year and name AND the identifier in the same column while Excel generates or looks up your identifier. How about this, use headers for row 1 with year in A1, name in B1, ID in C1, and Joined in D1.

Start your data in A2 & B2. In C2, enter =IF(COUNTIF(B$1:B1,B2)=0,IFERROR(MAX(C$1:C1)+1,1),VLOOKUP(B2,B$1:C1,2,0))
Then, in D2, enter =A2&" - "&B2&" - "&TEXT(C2,"000000")
 

charlie_3dc

New Member
Joined
Jan 28, 2015
Messages
5
Does it all need to be 1 column? You won't be entering the year and name AND the identifier in the same column while Excel generates or looks up your identifier. How about this, use headers for row 1 with year in A1, name in B1, ID in C1, and Joined in D1.

Start your data in A2 & B2. In C2, enter =IF(COUNTIF(B$1:B1,B2)=0,IFERROR(MAX(C$1:C1)+1,1),VLOOKUP(B2,B$1:C1,2,0))
Then, in D2, enter =A2&" - "&B2&" - "&TEXT(C2,"000000")
Unfortunately that doesn't seem to work.

My problem is very complex. Think I may have to purchase a database system to get what I require.

But thank you for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,846
Messages
5,465,053
Members
406,410
Latest member
Di27

This Week's Hot Topics

Top