changing text to numbers

[Angela]

I have a column (b2)that lists the last names of people on staff. I
want to post the number of times they were late without using their
name. Is there a way to change their names to a unique # and this be
consistent from month to month?

 

[Harlan Grove]

I have a column (b2)that lists the last names of people on staff. I
want to post the number of times they were late without using their
name. Is there a way to change their names to a unique # and this be
consistent from month to month?

Meaning you don't have social security numbers or employee numbers?

If the names never change (often not true for recently married persons), then
just add new names to a master list and use row number within the unsorted list
to identify the persons.

 

[Norman Harker]

Hi Angela
You could insert two new columns.

Column C is merely a range of numbers from 1 to the number of staff
you have. This will allow re-sorting to the original order.

Column C first insert a random number function =RAND()
Then freeze the random number by Selecting them all and then using
Copy Paste Special Values OK.

Sort your data on your random numbers and hide the columns with names
and you have your defaulters list.

You're unlikely to have duplicates but you could test this on your
random numbers using a formula from Chip Pearson:

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")

However, If you keep the same random numbers for all names from month
to month, it won't be long before someone starts to crack your random
numbers. But adding a new column every month will allow you to publish
the list, allow some (limited) privacy of "defaulter", limit the
ability to match numbers with names to a monthly basis, and allow you
the ability to keep track each month.

Can't say that I approve of publicizing such lists though. It might
have undesired effects like making those who habitually stay late
without pay but arrive the odd few minutes late. Or those who take
work home might decide not to. I'd tend to use non-spreadsheet
solutions if lateness is an issue. Big Boss is watching you and
keeping records doesn't tend to go down very well.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.