Populate Field(s) with Unique Value From Separate File

Hello All,
I have a file of 40,000 unique codes that need to be assigned to customer records, dependent on the quantity of items they purchased. For every pair of items the customer purchased, they are entitled to 1 unique code (so, 6 items purchased, 3 unique codes assigned). These codes need to be deleted from the source list so that they can't be mistakenly reassigned. The customer records file contains NAME, CODE1, CODE2, (etc, up to CODE6), and ITEM QUANTITY. I could put IF statements in each of the CODE# fields that will look at the ITEM QUANTITY field, and copy a value if TRUE, however, I'm not sure how to go about making sure that code is a unique one from the source list. Is it possible to use a CUT/COPY function?

Hope this is clear enough.

Thanks in advance!

Hi a68tbird

I'm not clear about how a code will be applied to a pair of items and not clear how a code will be put to 1 of 6 CODE columns. Other than that, it all seems quite straight forward.

Perhaps if you were to supply a sample of what you have it might make things more obvious?

Regards
Alastair

Thanks Alastair,
I have attached two sample files: one is a list of unique codes, the other the customer records file. Each record shows the quantity of the item the customer purchased. For every two items purchased, they receive 1 unique code. So, Customer1, purchased 2 items. CODE1 (at B2) needs a value from the CODES file. Customer4 purchased 5 items, so they are entitled to 2 codes - one in B5, one in C5.

As these codes are assigned, they must not be used again. So each time a code is pulled from the CODES file, it must be a new, unused value.

Hope this helps!

Hi a68tbird

I think that the attached CustomerRecords.xlsm (note not ".xlsx") will do what you want.

You will need to have your Codes.xlsx file open.

To run the macro press Ctrl+shift+Q and to see the code press Alt+F8 / step into.

I have not deleted the codes when used - just marked them (it makes testing much easier !).

Let me know if this is what you want.

Regards
Alastair

Wow! Thanks very much Alastair. This works perfectly. Very much appreciated!

You're welcome. Glad to have been of assistance

Regards
Alastair