Serial Number

[charlie_3dc]

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

 

[dya]

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]

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!