Anonymising ID numbers

[MAWFF]

Hello all,

Apologies if this is a silly question or if its a regularly posted question but I have a spreadsheet with data arranged in 3 columns as follows:

FamilyID.....ChildDoB.....ChildID
20000........29/09/03........36
20001........14/02/98........178
20002........18/01/86........9
20003.......21/04/94.....2589
20003.......08/03/91.....689
20004........30/11/99........3654

Some of the familyID's are duplicated where the family has more than one child registered with us.

What I am looking for is a way of anonymising the familyID but where the familyID is duplicated for 2nd or 3rd children to have the same anonymised ID so that the data would look like:

FamilyID.....ChildDoB.....ChildID
1............29/09/03........36
2............14/02/98........178
3............18/01/86........9
4...........21/04/94.....2589
4...........08/03/91.....689
5............30/11/99........3654

I have about 150,000 records in the workbook spread over 3 sheets - is this possible? If so how would I do it?

Many thanks in advance

Mark

 

[Oaktree]

Welcome to the board, Mark.

It depends on the level of anonymity you're trying to achieve. You could, for instance, add a column which has =right(a1) copied down and hide column A. That would still have the full ID in the file, but just won't display it.

Or, you could create a bridge file whereby you assign a random "key" to each ID, and use a vlookup to pull the new "key" into your file and delete the original ID.

Depends on what you're looking for

 

[MAWFF]

Thanks for the welcome and the tips.

I'm afraid I'm not that well up on Excel so I'm not sure of what you mean-my knowledge is pretty basic

Basically I have a dataset as outlined above and I need to provide this to a researcher (we are a UK based disability charity that sometimes provides research data to researchers).

However, the familyID's in the spreadsheet could be an identifying feature of the family under UK data protection law and therefore I can't supply it in that format.

Therefore I need someway of changing it to another number but maintaining those that are duplicated due to the family having more than one child. Just a plain sequential number would be fine for that purpose eg 1, 2, 3, 4..........etc. But where the 5th family have 2 (or however many) children their familyID would be changed to 5 for both children so the sequence would be 1, 2 3, 4, 5, 5 ........etc

If its of any consequence the childID is unique, only the familyID can be a duplicate

Hope this clarifies and thanks for the help

Best

Mark