Weird Validation Trick

What I'm trying to do is difficult to explain. I'm trying to create a data validated field with a list that is comprised of the entries made on that field. For instance, say the field is A1:A10. All cells are blank. When I enter text into any of the fields, A1:A10, that text is automatically added to the validation list assuming it is a unique entry. Duplicate entries are permitted, but they are not added to the list. Only unique entries are added to the list.

I have achieved this, but to a smaller scale than desired. See the example uploaded to my google.docs page:

https://docs.google.com/leaf?id=0B3X...thkey=CIWggOMC

I'm working on a spreadsheet that will contain 6,500 cells of potential data entry. These cells will be very much like cells A2:A12 in my example file. These cells will also produce a raw list, like cells I2:I12, and a "no repeats" list like in cells J1:J12.

The formula for the "no repeats" list in the example is

Please Login or Register  to view this content.

This isn't very processor intensive. However, when the range is $I$1:$I$6500, calculation becomes painfully slow.

I'm looking for a method for rooting out repeats that is not so demanding on the CPU. Every time I enter data on the entry field, the CPU is forced to check the other 6500 cells for repeats.

Thus far, I have attempted to break the list up into parts. Instead of 1 no "repeats list" for 6500, I had 13 "no repeats" lists for 500. Each "No Repeats" list fed to a master "no repeats" list. All in all, this reduced my cpu's workload considerably, or at least I thought. Unfortunately, this did not improve performance.

I'm at a loss. Does anyone know of a more efficient way to achieve this? I'd be up for a VBA solution, but I can't think of one. Any help with this would be greatly appreciated.

Weird Validation Trick