Prev: COUNTIF Function
Next: how do I drag and fill hyperlinks?
From: Confused on 19 Apr 2010 15:04
Is there a way to anonymize say a column of employee numbers in Excel? I want
it done in such a way that for each year of data the number is encrypted the
same way so i can use the encrypted value to look up data for that employee
without knowing who the person is? Thanks for any assistance you can provide.
From: JLatham on 20 Apr 2010 08:46
What's the end purpose of this hiding/encrypting of the employee numbers?
Just to hide them from you, or to hide from other people viewing the
reports/output later?

In either case you could 'hide' them by building a separate sheet that has 2
columns:
a series of sequential numbers in one column (A) and the employee ID numbers
in a column to the right of that column, say B. The data would look
something like:
A B
1 1 Emp493
2 2 Emp201
3 3 Emp004

Then you can enter numbers from Column A to refer to an employee, and if you
need the actual employee number for use, it can be done with VLOOKUP as:
=VLOOKUP(X99,CryptoSheet!$A$1:$A500,2,False)
where X99 would hold one of the numbers from column A on that extra sheet
(which can even be hidden from view).

An even more robust formula would be:
=VLOOKUP(X99,INDIRECT("CryptoSheet!A1:B" & COUNTA(CryptoSheet!A:A)),2,FALSE)
which uses the count of entries in column A of the CryptoSheet to determine
what range to do the lookup for - so no maintenance to the formula(s) when
you add or remove employees from the list on that sheet.

If you need keep the real employee IDs invisible to other users that you
might distribute the workbook to, then put CryptoSheet in a workbook on your
computer only. That's going to result in: having to modify the formulas to
look in that other workbook for the lookup table, and getting "update links?"
prompt when opening the primary book which you'll have to decide on how to
deal with.

"Confused" wrote:

> Is there a way to anonymize say a column of employee numbers in Excel? I want
> it done in such a way that for each year of data the number is encrypted the
> same way so i can use the encrypted value to look up data for that employee
> without knowing who the person is? Thanks for any assistance you can provide.
 | 
Pages: 1
Prev: COUNTIF Function
Next: how do I drag and fill hyperlinks?