Same user ID for multiple entries

[Fester675]

Hi all

I have a column of data with Users names and a column of User IDs - the ID is to anonymise and not have the ability to trace an individual.

So for example - 3 entries for John Smith; 1 entry for Jane Doe; 5 entries for Sam Brown....

so the 3 John Smith entries will have the same user id = 001; Jane Doe = 002; Sam Brown = 003 - even though there are 9 rows of data.

Any formula that can help me do this?

TIA

 

[Marcelo Branco]

There many ways to achieve this. To avoid complex formulas I suggest...

	A​	B​	C​	D​	E​
1​	Name​	ID​		Unique List​	ID​
2​	John Smith​	001​		John Smith​	001​
3​	Jane Doe​	002​		Jane Doe​	002​
4​	John Smith​	001​		Sam Brown​	003​
5​	Sam Brown​	003​
6​	John Smith​	001​
7​	Sam Brown​	003​
8​	Sam Brown​	003​
9​	Sam Brown​	003​
10​	Sam Brown​	003​

Create a unique list (D2:D4) - you can use Data > Remove duplicates

Formula in E2 copied down
=TEXT(COUNTA(D$2:D2),"000")

Formula in B2 copied down
=IF(A2="","",VLOOKUP(A2,D:E,2,0))

M.