Generate 6 digit numbers based on cell value

**onp** · 02-23-2020, 10:21 PM

Hello,
Is there a formula that would generate a six or more digit number based on a cell value that contains text and numbers?
I have been doing lots of formula tests and have not been able to get favorable results.

Example:
Cell A1 contains the following value "100' Talon command Cable 7 Pin Lemo"
Cell A2 contains the following value "12" 2Pin Lemo/Xlr Sraight Thrugh Camera Power Pigtail"

Cell B1 would get a result of six digit number based on A1 values.
Cell B2 would get a result of six digit number based on A2 values.

And if I copy value from A1 to any cells in column A, the same number should follow and not change.
The generated number should always be associated to the value

Thank you so much for your time and help

Cheers
Nino

**Sam Capricci** · 02-23-2020, 10:32 PM

maybe use a table of the text values and the numbers you want, then do a vlookup or index match against that table?
For a better answer maybe upload a sample workbook WITH expected results and we can give you a better answer.
the instructions are at the top of the post in the yellow banner for how to upload a workbook.

**onp** · 02-23-2020, 10:52 PM

Hello Sambo
I have attached a sample file for reviewing
Thank you

**Sam Capricci** · 02-23-2020, 11:00 PM

so, you don't already have a six digit number for these products? You want excel to generate one for a product then every time you have that product you want excel to use it again, is that correct?

**onp** · 02-23-2020, 11:05 PM

Yes Sambo. Correct

**Sam Capricci** · 02-23-2020, 11:16 PM

you can use =RANDBETWEEN(100000,999999) to generate random six digit numbers but they will change every time you hit enter or save on the workbook. If your list is not too big it is likely you will not get any repeated numbers in the list.
so I'm uploading your workbook with my "solution" as it is. But to get something cleaner I think you might need VBA.
So...
step 1, make a distinct list of your description of items that are in your data entry sheet, (copy list into another workbook and use remove duplicates)
step 2, remove duplicates
step 3, use randbetween formula
step 4, copy random numbers and paste special >> values so they do not change next time you use them.
step 5, use vlookup in your data entry sheet to bring over the six digit numbers.
(attached example)

**rondeondon** · 02-23-2020, 11:19 PM

I'd use a lookup for the rows above, to determine if the description has occurred before, if it has use vlookup to return the product number, if not then add 1 to the max of the prod id's above.
formula in j39 of your file attached: =IFERROR(VLOOKUP(D40,$D$2:$J39,7,FALSE),MAX($J$3:J39)+1)
if you manually set a number it will use that instead, but being a vlookupup it will take the first example it finds. Also being based on the description is needs to match 100% or it will consider it a new description.

**onp** · 02-23-2020, 11:28 PM

I have tried that method,
But sometimes I Need to change the description of the item or add more items
and that's done on a very short notice. literately speaking, seconds. (meaning while I am on the phone with production)
To take all the above steps during Prep it would a nightmare for me

that's why I was hoping for an automated way

**Sam Capricci** · 02-23-2020, 11:32 PM

Post #8, I'm not sure if you are referring to my proposed solution or Ron's.
But if formulas will not work then you might want to private message a moderator to request the post be moved to the VBA forum as I think that might be your only solution.

**onp** · 02-23-2020, 11:42 PM

Sambo, thanks for trying
how do I PM a moderator to move my post to VBA?

**onp** · 02-23-2020, 11:53 PM

Hello,
Is there a formula that would generate a six or more digit number based on a cell value that contains text and numbers?
I have been doing lots of formula tests and have not been able to get favorable results.

Example:
Cell A1 contains the following value "100' Talon command Cable 7 Pin Lemo"
Cell A2 contains the following value "12" 2Pin Lemo/Xlr Sraight Thrugh Camera Power Pigtail"

Cell B1 would get a result of six digit number based on A1 values.
Cell B2 would get a result of six digit number based on A2 values.

And if I copy value from A1 to any cells in column A, the same number should follow and not change.
The generated number should always be associated to the value

See attached Sample

Thank you so much for your time and help
Cheers
Nino

**FDibbins** · 02-24-2020, 01:56 AM

why would you give an example in your thread of A1 and B1 - and sample data, then upload a sample file that bares absolutely zero reference to those cells?

**FDibbins** · 02-24-2020, 01:58 AM

Also, perhaps provide a crystal ball that shows how you conjured up those numbers in col J...if in fact that IS what you were trying to create?

If they are indeed arbitrary numbers, what on earth DO you want the numbers to look like?
remember, we are neither mind readers now magicians here lol

**FDibbins** · 02-24-2020, 02:03 AM

I have merged this thread with your other (not permitted) duplicate thread here....
https://www.excelforum.com/excel-pro...ell-value.html

**onp** · 02-24-2020, 02:12 AM

Hello FDibbins,
You are right about the sample file bearing zero references. Sorry.
This is what my original post should have been, and the uploaded sample would match.

Example:
Cell D1 contains the following value "10' 4 Pin Xlr 16 Gauge Talon Power Cable"
Cell D2 contains the following value "10' 4Pin Xlr 14 Gauge Grey Talon Power Cable"

Cell J1 would get a result of six digit number based on D1 values.
Cell J2 would get a result of six digit number based on D2 values.

And if I copy value from D1 to any cells in column D, the same number should follow and not change.
The generated number should always be associated to the value
As per the Numbers in Column J, they have no reference. I typed them to project a desired solution.

See attached Sample

Late at night and iI am still trying.

**Crooza** · 02-24-2020, 02:16 AM

I just entered j3 = 100001 then use this in J4 and drag down (provided you don't want random numbers

Please Login or Register  to view this content.

**onp** · 02-24-2020, 01:46 PM

Hello Gents,
I wanted to Thank everyone for trying to Resolve my request.
I wanted to share with you my request has been Solved and I am very happy.
Now I can have Unique numbers based on Cell Value and they will stay the same regardless of where I place The item.
I have attached the Sample sheet with all the Formula and code. In the event someone out there may want a similar function

Thank you All
Cheers
Nino

Generate 6 digit numbers based on cell value

LinkBack

Thread Tools

Rate This Thread

Display

Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Re: Generate 6 digit numbers based on cell value

Thread Information

Users Browsing this Thread

Similar Threads

How to generate 16 digit unique random alpha numeric numbers?

Generate non-repeating random Double-Digit numbers

[SOLVED] Code to generate 2000 unique 8 digit numbers

[SOLVED] VBA to generate random 5-digit serial numbers using

[SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file

Generate consecutive numbers based on the value of a cell

How do I generate a list of 5-digit numbers in Excel?

Bookmarks

Bookmarks

Posting Permissions