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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

ID​

[/td][td][/td][td]

Unique List​

[/td][td]

ID​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John Smith​

[/td][td]

001​

[/td][td][/td][td]

John Smith​

[/td][td]

001​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Jane Doe​

[/td][td]

002​

[/td][td][/td][td]

Jane Doe​

[/td][td]

002​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

John Smith​

[/td][td]

001​

[/td][td][/td][td]

Sam Brown​

[/td][td]

003​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Sam Brown​

[/td][td]

003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John Smith​

[/td][td]

001​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Sam Brown​

[/td][td]

003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Sam Brown​

[/td][td]

003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Sam Brown​

[/td][td]

003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Sam Brown​

[/td][td]

003​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.