Dec2Hex 16 digit none reapeating number referencing A:A

[NotAnotherExpert]

I will admit, I am a bit of a noob when it comes to Excel anymore. I am trying to make an Excel Document that will hold a set of serial numbers in a hex format that will not repeat and can be referenced from outside excel to be printed onto labels for my company. I found a formula to create a 16 digit random hexadecimal that I can use, however I don't know how to make it look at A:A to make sure it is not repeating a SN I have already used. Currently I am using

"=DEC2HEX(RANDBETWEEN(0,2^16),8) & DEC2HEX(RANDBETWEEN(0,2^16),8)"

and it is giving me a workable number, and I can drag it down to create as many fields as I want. However, every time I open and close it the numbers change. What I would like to do is have a single cell that contains the formula I am using and reference out to A:A where I will put the used numbers.

Any help would be greatly appreciated!

 

[pbornemeier]

The formula in your post will return a value such as 0000xxxx0000xxxx where each xxxx is an independent 4-character hex number.

This formula will give you a random, 16 character hex number:

=DEC2HEX(RANDBETWEEN(0,2^16),4) & DEC2HEX(RANDBETWEEN(0,2^16),4)& DEC2HEX(RANDBETWEEN(0,2^16),4)& DEC2HEX(RANDBETWEEN(0,2^16),4)

Make the column of hex numbers 1% larger than the required count using the above formula.
Convert to text with copy/paste special values
Use advanced filter to copy to a new location with unique values only (or use Data | Remove Duplicates if Excel 2010 or higher)
Delete original column
Delete rows greater than the required quantity.

Note: Decimal 2^16 -1 = Hex FFFF

 

[NotAnotherExpert]

Thank you very much! I will try this and let you know how it works!

 

[pbornemeier]

Was dupe response.

 

[NotAnotherExpert]

This does give me the random 16 digit code I need and makes it so I can copy the values out with ease, I thank you very much!

I am curious if there is a way I can include into the formula to check for duplicates in the column I am putting the data?

For example: I put the randomly generated Hexadecimal formula into F:5 and I am putting the numbers it is generating into A:1, A:2 and so forth down.

Because I am going to export the data to another program that will print the SN into a label. Also I am writing code in C++ ((just picked it up last week)) to push this SN# into some test equipment that will push back to me raw data that will go into another spreadsheet with the generated SN. We are trying to automate as much as possible.

 

[pbornemeier]

If the hex number you want to check is in cell A1 and the already used hex numbers are in A2 and below then putting this formula in B1:

=COUNTIF($A:$A,A1)

and copying it down will show a 1 if the number in A1 is unique and more than 1 to show if there are that many duplicates in the column

If you copy the B1 formula down, you will see all ones if all of the numbers are unique and more than 1 next to any that are not.

Or to make it fit your request

Put this formula in G5

=COUNTIF($A:$A,F5)

0 will indicate that F5 is not repeated in column A, 1 or more will indicate that it has been used.

 

[NotAnotherExpert]

Thank you very much, I will try this tomorrow at work. This should work for what I am trying to do