Finding next available number with addition of Left function

**Samba1** · 07-02-2012, 05:23 PM

Howdy all -

I've spent way too much time trying to find an answer. There's a lot of threads that are close, but I just can't piece it together. I have a list of 12 digit UPC codes and I need to find the next available number while ignoring the 12th digit (check digit). For example -

818411000331
818411000348
818411000355
818411000379

The returned number would be 81841100036, then I would add the calculations for the check digit. I was able to get the following to work using all 12 digits, but I can't get the "Left" function to work -

OFFSET(C12:C47,MATCH(FALSE,(C12:C47=C12:C47+1),0)-1,0)+1

Thanks!

**oeldere** · 07-02-2012, 05:37 PM

You get better help if you post an Excel-example of your workbook, without confidential information.

Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

Use BEFORE/AFTER sheets if that helps make it clearer.

**shg** · 07-02-2012, 05:46 PM

       -----A----- ------B------- C -----D------
   1     Number    W/ check digit   First Unused
   2   11242263959   112422639598    11242263965
   3   11242263960   112422639604               
   4   11242263961   112422639611               
   5   11242263962   112422639628               
   6   11242263963   112422639635               
   7   11242263964   112422639642               
   8   11242263966   112422639666               
   9   11242263967   112422639673               
  10   11242263968   112422639680

In D2, confirmed with Ctr+Shift+Enter,

=INT(INDEX(B2:B25, MATCH(TRUE, INT(B3:B25/10) - INT(B2:B24/10) > 1, 0))/10) + 1

**shg** · 07-02-2012, 06:22 PM

corrected prior post

**Søren Larsen** · 07-02-2012, 06:39 PM

Then I'll give it a go:

Formula:

=IFERROR(LEFT(INDEX($A$1:$A$4,MATCH(TRUE,LEFT($A$1:$A$4,11)*1<>LEFT($A$2:$A$5,11)*1-1,0)),11)*1+1,MAX(LEFT($A$1:$A$4;11)*1)+1)

Confirmed with Ctrl+Shift+Enter.

**shg** · 07-02-2012, 06:44 PM

Soren's works for me also.

**Samba1** · 07-02-2012, 07:27 PM

Soren's worked perfectly! Thank you, thank you!!!

I can't remember how to switch to solved…? Got it!

**Samba1** · 07-02-2012, 07:55 PM

Sorry - spoke too soon. Once I put everything together, the code just assigns the next number even if it already exists. Did I miss something? I've attached the worksheet.

Thank you!

Sample.xlsm

**Søren Larsen** · 07-03-2012, 03:45 AM

The formula I provided, and shg's as well I beleive, assumes that your numbers are ordered sequentially with regards to the first 11 digits.

**Samba1** · 07-03-2012, 01:24 PM

I've added coding to sort the numbers after I insert the new UPC, but now I have a new problem. I'm sure it's an easy one, but it's driving me crazy! Everything is formatted as numbers, but when the generated UPC is pasted the numbers are changed to text - even though the cell format shows "number" (I get an error in the cell that it's formatted as text). As a result, the new numbers are not being sorted. Any idea why this is happen? WB is attached - please excuse my "coding".

Sample2.xlsm

**Søren Larsen** · 07-03-2012, 01:56 PM

It's because Excel has it stored as text, I don't why that is in your case. Maybe it has to do with how the UPCs are generated or inserted. You can just click "Convert to number" in the menu that appears when clicking the error-message that appears when hovering over the cell. If you select both cells first, then you need only do it once. You can also just enter the cells one by one and hit Enter, which reevaluates the cell contents.

**oeldere** · 07-03-2012, 02:20 PM

Row 82

These two sets of numbers are giving me a text error		818411000362	10818411000369

Probably I don't get the question, because I can count with those 2 cells (Excel 2007).

**Samba1** · 07-03-2012, 02:15 PM

Unfortunately, I don't get the error before pasting so I can't convert the numbers until everything is done. I was able to change the sort to SortTextAsNumbers so that helped. It appears that the function below is "forcing" the cell value to be text - is there any way to force it to be a number? -

=(F5&MOD(10-MOD(SUMPRODUCT(MID(F5,{1,2,3,4,5,6,7,8,9,10,11},1)*{3,1,3,1,3,1,3,1,3,1,3}),10),10))

**Søren Larsen** · 07-03-2012, 02:37 PM

Yes, just multiply it by 1:

Formula:

=(F5&MOD(10-MOD(SUMPRODUCT(MID(F5,{1,2,3,4,5,6,7,8,9,10,11},1)*{3,1,3,1,3,1,3,1,3,1,3}),10),10))*1

**Samba1** · 07-03-2012, 03:21 PM

Oh my goodness. Thank you!!!

**Søren Larsen** · 07-03-2012, 03:22 PM

You're welcome!

Finding next available number with addition of Left function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Re: Finding next available number with addition of Left function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions