for frank to be 1113 it added 1 to the value for bingo above
check that the max function is B$2:B2
because it did not find the max of the values above.

"Proton" wrote:

Close, but not quite. I tried it out on a test sheet and here's what I got:

FamilyName FamilyNum
Alice 1111
Bingo 1112
Charlie 1113
Daniel 1114
Bingo 1112
Frank 1113
Alice 1111

It makes some of them unique, but Charlie and Frank share 1113. (But it's
certainly closer than what I had before!)


"bj" wrote:

if amily name in A and num in b
then try
in B2
Assuming Row one is headder
enter the first unique numberyou want (In your example it would be 1111)
in B3
=if(countif($A$2:A3,A3)1, vlookup(A3,A:B,2,0),Max($B2:B2)+unique number
increment)
assuming you want more than 1 as the unique number increment (in your
example it would also be 1111)
copy and paste down as far as you want.



"Proton" wrote:

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.