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

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

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