Or, you could just sort the names then:

Enter 1 in B2

Enter this much simpler formula in B3 and copy down as needed:

=IF(A3=A2,B2,B2+1)

Biff

"T. Valko" wrote in message
...
Try this:

Assume the names are in the range A2:An

Enter 1 in B2

Enter this formula in B3 and copy down as needed:

=IF(COUNTIF(A$2:A3,A3)1,VLOOKUP(A3,A$2:B2,2,0),MA X(B$2:B2)+1)

Biff

"Proton" (nospam) wrote in message
...
Using either Excel 2003 or 2007, doesn't matter to me.
I have a set of data over many sheets, that I'm currently consolidating
into
one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson,
etc.". Some of the names in this column are repeated.
What I need to do is take that column, find the unique names, and convert
them into unique number values into another column.
For example:

FamilyName FamilyNum
Jones 1111
Smith 2222
Wilson 3333
Rogers 4444
Smith 2222
Johnson 5555

Is something like this even possible? thanks for any advice.