I am after some help with a small computing task.  Basically I want to acquire a two column list, the first column containing numbers 1 to 20,000.  The second column is the bugger in that I want it filled with a 6 character set of random letters which are not repeated.  I then want a program which will take an Excel spread sheet (or csv file) and use the two columns as a look up table to switch each indivdiual number to a unique 6 character value.  The request arises from a task in which I want to transfer BCA membership numbers into an equivalent unique set of characters to anonymise the data.  I can then pass on the changed data to someone who can do some analytical work on the data sets without compromising the Data Protection Act.  Please PM me if you can help.

Have you had an answer on this one yet? If not, PM me and I'll email you an Excel spreadhseet that does it for you (and proves its been done).

I am sure I could come up with a VBA script to do this but off to training. It would run in Access or a compiled executable and use the OutputTo method, ACFormat XLS or whatever
Been a long time though since I last did this stuff.

The programming/statistics language R and the accompanying R-studio software would be relatively easy for this, they are both free and there is tons of up-to date help online/in books to get used to them.

You can read data into the software with .csv and text files (also many others) and write a R script that does almost anything you want. Beware it may take some time to learn but is invaluable for statistics and a number of other functions.

One of my mates from the NPC did some work with an advertising analysis company and another friend works on biological sampling. I will point both of these to this topic.

The link below might help.

If you read far enough down what he is suggesting, in the contest of your problem, is:

Col 1 = 1 to 20,000

Fill col 2 with 20,000 non-repeating sets of 6 characters - easy enough to do because at this stage you are putting them in a systematic order, so you can ensure there are no repeats. (may be easiest to do this on a working sheet, generate values in 6 cols, then concatenate them into a single 6 digit string)

Fill col 3 with random numbers using RAND.

Then SORT cols 2 and 3 on the basis of the value in 3. Col 2 will now be in random order and you have what you want.

http://www.atrandom.iansharpe.com/excel-random.php

many thanks for the support.  I have now been promised two solutions and Kay has indicated a simple way to do it myself in Excel.  Hopefully one of them will do the trick (that is when I get the time to do it).

You can make it Kay's method even simpler using =ROW() to make the new ID.

Thanks to Andys for producing a neat piece of coding in Excel using simple functions which works to produce the look up table.  I have then been able to use the VLOOKUP function in Excel to switch numbers.  Great - I thought that was going to be a real headache.