How to bulk change cell values?

[DrMinkleman]

Hi all,

I have a long list of email addresses from my business's newsletter subscription list, most of which are repeated multiple times within the list.

I'm going to send this email list to someone to do some analysis, so I want to make sure the don't have access to the real email addresses. I need to find a way to anonymize each email address. For example, every time john[at]smith.com appears, it needs to be replaced with User0000001 and every time that mary[at]jones.com appears it needs to be replaced with User0000002.

When the list is returned, I need to be able to "un-anonymize" the list and return "User0000001" back to john[at]smith.com.

Can someone help with a way to do this?

Many thanks in advance.

 

[Yongle]

One way

Create Index
1. Create a new sheet and name it "Register"
2. Copy the column containing the email addresses to "Register" column A
3. Select the whole of column A \ select DATA tab \ (under Data tools) select Remove Duplicates\ "OK"
4. Enter User0000001 in cell B2
5. Point cursor in bottom right corner of cell B2 until pointer shape changes to + and double-click (to fill all the cells to last record)
6. Copy Column A to column C (to provide a 2 way lookup)

"Register" looks like this
[TABLE="width: 510"]
<tbody>[TR]
[TD]Address
[/TD]
[TD]Code[/TD]
[TD]Address
[/TD]
[/TR]
[TR]
[TD]abcd01@def.com[/TD]
[TD]User0000001[/TD]
[TD]abcd01@def.com[/TD]
[/TR]
[TR]
[TD]abcdef@deffffffff.com[/TD]
[TD]User0000002[/TD]
[TD]abcdef@deffffffff.com[/TD]
[/TR]
[TR]
[TD]abcd01@bdkff.com[/TD]
[TD]User0000003[/TD]
[TD]abcd01@bdkff.com[/TD]
[/TR]
</tbody>[/TABLE]

Create List for Analyst
1. Select your original sheet
2. Right click on sheet tab \ select Move or Copy \ check Copy box \ "OK"
3. Rename the sheet "Analyst"
4. Insert a column immediately to right of the list of email addresses
5. Iin row 2 enter this formula
=VLOOKUP(A2,Register!A:B,2,0)
(assumes your email addresses are in column A in sheet Analyst)
6. Point cursor in bottom right corner of cell until pointer shape changes to + and double-click (to fill all the cells to last record)
7. Select the column \ right click \ select Copy \ right click \ select paste values
8. Delete the column containing email addresses

To convert it back afterwards
1. Same process as above (create column etc)
2. Formula changes to:
=VLOOKUP(A2,Register!B:C,2,0)

 

[DrMinkleman]

Thanks so much! It worked perfectly.