Help Pls Copy 1 Random Result To Many On Another Sheet?
Thank you!
Now that's clever :)
"Jacob Skaria" wrote:
Instead of VLOOKUP() use the below formula which will return the nth lookup
value.
To return 2nd match....(return from ColB based on match in ColA)
C1 = lookup_value
C2 = 2
=INDEX(B1:B100,SMALL(IF(A1:A100=C1,ROW(A1:A100)),C 2))
Instead of C2 you can genrate a random number between 1 and the count of the
lookup_value in ColA. between 1 and COUNTIF(A:A,C1)
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"
If this post helps click Yes
---------------
Jacob Skaria
"narrator" wrote:
Hi,
I need your help.
Sheet 1: Produces a random alphanumeric string in about 200 variations,
depending on strict criteria.
eg. Criteria #23 is 2 letters, 3 numbers, a letter, a number - such as MH735D9
Sheet2: Has 50,000 items. It uses data in its page to decide which string
code it should use from sheet 1.
eg. Line 16843, item fits with variation #23, vlookup sheet 1 and get unique
code.
Trouble is, because it is already calculated on sheet 1, all the thousands
of items that fit criteria #23 will get the same code from the vlookup,
rather than a unique code.
eg. from above, all items fitting criteria #23 will get the code MH735D9
How can I make it so that they all have unique codes (as per the criteria)?
Thanks in advance. :)
|