(just sexing up the forum for our Guardian readers 😀 )

I have a long spreadsheet that has a number of columns, one of which contains an identifier number code which is unique to an individual person

Each number may appear any number of times within the sheet

I need to anonymise the data so want to assign new, unrelated values instead of each identifier but same one every time it appears in the sheet. I can’t see how I now automatically assign new identifers (I’m sure there must be a way of numbering, maybe sequentially) I haven’t sorted the data by this number but I suspect I need to.

so if I have 2 students, 123 and 234 who took tests A,B,C, they might have

pass 123 test A
fail 234 test A
fail 123 test B
fail 234 test B
pass 123 test C
pass 234 test C

I want it to read:

pass 1 test A
fail 2 test A
fail 1 test B
fail 2 test B
pass 1 test C
pass 2 test C

(it’s not possible to truncate their identifier just to the 1st digit – that’s just how my example works. The real ones have almost random 4-7 digit numbers)

Anyone ?? 🙁

ooh will I be fast enough. Hang on

Get a list of all the identifiers and put them in the first column on a new spreadsheet. In the next column put the identifier you want to replace it with.
On the original spreadhseet, add a column to the right of the one with the identifiers. Use the Vlookup function to search for the identifier in the list on sheet 2 and bring back the value in the second column.

This is the formula you need:
=VLOOKUP(Sheet1!E1,Sheet2!A1:B2,2,FALSE)

Get a list of all the identifiers

I can’t – or rather I’d have to manually delete several thousand duplicates to get it

WAIT !!!!

(I know how to deduplicate; that’ll do it, hey ?!)

I’m sure there’s an easy way to deduplicate; but if there isn’t then just do it the more complex way:
Put all the raw identifiers in the first column of a sheet.
Sort them so like values are next to each other.
In the second column, add a formula to compare each value to the one above it. If it’s the same return 1; if different then return 0. Copy n paste that formula down the whole column.
Voila, the first instance of each identifier is marked with a 1. Filter the list to show only zeros. |Select all the visible rows and delete them. Then take the filter off again and you have a deduped list.

dedup’s easy* – thanks thegen !

(* in 2007 anyway, just a button on the menu bar)

Right. I’ve tidied it up a bit:
These are the entries in the first four columns.

pass| 123| =VLOOKUP(B1,Sheet2!A$1:B$2,2,FALSE)| test a
fail| 234| =VLOOKUP(B2,Sheet2!A$1:B$2,2,FALSE)| test a
fail| 123| =VLOOKUP(B3,Sheet2!A$1:B$2,2,FALSE)| test b

Assuming this data exists on the second tab:

123| bob
234| bill

Then the first tab will actually display:

pass| 123| bob| test a
fail| 234| bill| test a
fail| 123| bob| test b

All done – thanks

What the hell are you on about? 😕