Finding unique names--then converting those names to unique number

[Proton]

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.

 

[bj]

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.

 

[T. Valko]

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),MAX(B$2:B2)+1)

Biff

 

[Gary''s Student]

1. put all you names into a single column with a label to the top of the
column.
2. Data > Filter > Advanced FIlter > copy to new column/unique records only

For example:

item item
cat cat
cat hat
hat bat
bat rat
rat dog
dog mouse
dog
hat
hat
mouse
mouse

The second column are the uniques
In C2 enter:
1000
In C3 enter:
=C2+1 and copy down:

item item
cat cat 1000
cat hat 1001
hat bat 1002
bat rat 1003
rat dog 1004
dog mouse 1005
dog
hat
hat
mouse
mouse

Now each name has a unique number. From anywhere in the spreadsheet, if you
want the number for a name, just VLOOKUP() the name in column B to get the
value in column C.

 

[Proton]

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!)

 

[Farhad]

Hi,

enter 1111 in the cell B2
Try this formula in the cell B3:

=IF(ISNA(VLOOKUP(A3,$A$2:B2,1,FALSE)),B2+1,VLOOKUP(A3,$A$2:B2,2,FALSE))

It should work.

Thanks,

 

[bj]

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.

 

[T. Valko]

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