Help Pls Copy 1 Random Result To Many On Another Sheet?
 

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. :)

Help Pls Copy 1 Random Result To Many On Another Sheet?
 

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. :)

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. :)