Identify first instance of duplicate entries, number them and loop until the end

[Jockster]

A simple table has four columns: ID number, Name, Year, Time.
Over the years (since 1960), more and more data has been added with each new name receiving the next available ID number. Some names are unique, others have been entered multiple times.
Once the table is sorted by year then by time, I would like to identify the first entry for each ID and allocate it a number (column E) to show the order in which people first did the challenge regardless of how many times they have actually done it.
By doing this, I would be able to tell who was the 50th person or the 100th person to do so and ultimately, how many unique names have completed the challenge.

I am unsure of how to even start looking at this but I do have a separate list of ID numbers (in column M but not in the table).
Starting at the first entry in this list, VBA would search down column A until if found the first match then add "1" to a new column (E).
The second number would be searched for and "2" placed in column 'E' and so on until the end of the list of unique IDs.

All help appreciated!

 

[mumps]

Some names are unique, others have been entered multiple times.

Are you saying that some ID's have also been duplicated?

 

[Eric W]

VBA is certainly an option is that's what you want, but keep in mind you can do this with a formula too:

Book1
	A	B	C	D	E
1	ID	Name	Year	Time	First Time?
2	100		2001	1	1
3	101		2002	2	2
4	100		2003	3
5	102		2004	4	3
6	100		2005	5
7	103		2005	6	4
8	100		2005	7
9	102		2005	8
10	105		2006	9	5
11	102		2007	10
12	102		2008	11
13	102		2009	12
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=IF(COUNTIF($A$1:$A1,A2)=0,MAX($E$1:$E1)+1,"")

Let us know if this suffices, or if you still want a macro.

 

[Jockster]

This formula has done exactly what I was after. Thank you very much!

 

[Eric W]

Glad we could help!