Changing values in cell to incrementing numbers

  • Thread starter Thread starter redmist
  • Start date Start date
R

redmist

Hi everyone,

Hope someone can help me again! I have a spreadsheet with severa
columns. The first contains email addresses - the rest are results fro
a survey.

I need to pull out the data but must hide the email addresses (dat
protection). However the problem is that the email addresses are th
only way of identifying the each answer came from a differen
individual. So I need to replace them with unique identifiers - I'
thinking numbers 1 - 1000 (approx). The column looks a little lik
this:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

In this instance I'd want the above replaced with:

1
1
1
2
2
3
3
3

... hope that makes sense! Can anyone give me pointers on how t
accomplish this?

Any help much appreciated...

Thanks!

John
 
I'd use a helper column.

Say your data is in A2:Axxx (header in row 1)
Insert a new column A.
Then in B1, put this formula:
=IF(B2=B1,A1,A1+1)
and drag down.
select column A
edit|copy
edit|paste special|values
delete column B

If you leave column B in the worksheet, but hide it and protect the worksheet,
it won't stop anybody curious from finding out what was there.

(Ps. This assumes that your data was sorted.)
 
Back
Top