Random Number Generator, but duplicate entries receive the same number

[wernstrom]

Hello all,

Hopefully the title says it all, but here is a bit more info.

I'm creating a mailing list where each recipient is assigned to a cohort. I have an excel table dataset with 5000+ rows, and quite a few emails appear twice.

To differentiate the emails, I made a quick n dirty COUNTIF column. if the cell=1, it is the first or only instance of that email. If cell>1, it must be a duplicate.

I wish to assign the first instance of an email address with a random number, then all subsequent instances of that email address with the same number.

I've tried using a VLOOKUP but I get a circular error:

=IF([@uniqueemail]=1,RANDBETWEEN(1,5),IF([@uniqueemail]>1,VLOOKUP([@email],Table1346,16,FALSE)))

..where 16 is the random number column. The theory is, if the email found to be a duplicate, the vlookup function should look at the email column, find the first instance of that email, look at the random number column, and return that value. It makes sense that I'm getting a circular error, but having a tough time trying to work a way around it.

Any help would be greatly appreciated

 

[wernstrom]

aaaaah naturally as soon as I pressed send, I fixed my own problem.

Had to adjust the range in VLOOKUP so that the first column aligned with my lookup value.