Anonymising ID numbers

MAWFF

New Member
Joined
Jan 25, 2005
Messages
2
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

New Member
Joined
Jan 25, 2005
Messages
2
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 :oops:

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
 

Forum statistics

Threads
1,147,507
Messages
5,741,566
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top