Automatic Carton Numbering

[rshenk3]

Hello and thank you,

So here's the deal.  I have a spreadsheet with the entire selection of possible products ordered from us by a customer.  I would like to be able to receive a PO from the customer, fill in the quantities ordered for each item in column R, delete the unused rows, and have the box numbers automatically populate in column H. 

I added a dummy row in row 2 to seed the formula.

=","&TEXTJOIN(",",TRUE,SEQUENCE(INDIRECT("J"&ROW()),1,1,1))

I use column V to create the box numbers.

=IFERROR(","&TEXTJOIN(",",TRUE,SEQUENCE(INDIRECT("J"&ROW()),1,((RIGHT(INDIRECT("V"&(ROW()-1)),LEN(INDIRECT("V"&(ROW()-1)))-SEARCH("$",SUBSTITUTE(INDIRECT("V"&(ROW()-1)),",","$",LEN(INDIRECT("V"&(ROW()-1)))-LEN(SUBSTITUTE(INDIRECT("V"&(ROW()-1)),",",""))))))+1),1)),","&(RIGHT(INDIRECT("V"&(ROW()-1)),LEN(INDIRECT("V"&(ROW()-1)))-SEARCH("$",SUBSTITUTE(INDIRECT("V"&(ROW()-1)),",","$",LEN(INDIRECT("V"&(ROW()-1)))-LEN(SUBSTITUTE(INDIRECT("V"&(ROW()-1)),",","")))))))

I remove the first comma in column H for the end result.

=TEXTAFTER(V3,",")

All this works as planned except I am having a hard time figuring out how to have the row immediately following the dummy row to start at 1 instead of 2.  Also, once I get something to work as hoped for I don't mess with it, so sorry for the messy formulas.  Any cleanup tips would be much appreciated as well.  Attached is the spreadsheet.  Thank you for your help!

[#2]

Hi @rshenk3,

you can try to solve the problem of automatically numbering cartons in Excel with a dummy row by using the following steps:

1. Create a dummy row in row 2 with the following formula in cell H2:

=IFERROR(TEXTAFTER(V2,","),1)​

This formula will check if the value in cell V2 is empty. If it is, then the formula will return the number 1. Otherwise, the formula will return the value in cell V2, after removing the first comma. 2. In cell H3, enter the following formula:

=IFERROR(TEXTJOIN(",",TRUE,SEQUENCE(J4,(RIGHT(V3,LEN(V3)-SEARCH("",SUBSTITUTE(V3,",","",LEN(V3)-LEN(SUBSTITUTE(V3,",","")))))+1),((RIGHT(V3,LEN(V3)-SEARCH("",SUBSTITUTE(V3,",","",LEN(V3)-LEN(SUBSTITUTE(V3,",",""))))))+1),1)),V3)

This formula will check if the value in cell V3 is empty. If it is, then the formula will return the value in cell V3.
Otherwise, the formula will create a comma-separated list of numbers, starting at the value in cell J4 and incrementing by 1, up to the value in cell V3. 3. Copy the formula in cell H3 down to the rest of the column. 4.

To remove the first comma in column H, use the following formula in cell H4:

=MID(H4,2,LEN(H4))

1. Copy the formula in cell H4 down to the rest of the column.

This will automatically number the cartons in column H, starting at 1, and skipping the dummy row in row 2.

To further clean up the formulas, you could use the following formulas:

=TEXTJOIN(",",TRUE,SEQUENCE(J3))

This formula will create a comma-separated list of numbers, starting at the value in cell J3 and incrementing by 1.

=IFERROR(TEXTJOIN(",",TRUE,SEQUENCE(J4,RIGHT(V3,LEN(V3)-SEARCH("",SUBSTITUTE(V3,",","",LEN(V3)-LEN(SUBSTITUTE(V3,",",""))))+1,((RIGHT(V3,LEN(V3)-SEARCH("",SUBSTITUTE(V3,",","",LEN(V3)-LEN(SUBSTITUTE(V3,",",""))))))+1),1)),V3)

Important (useful) hints:

• Make sure that the dummy row in row 2 is empty.
• Make sure that the values in column J are unique.
• Make sure that the values in column V are correct.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

I'm sorry but this doesn't seem to solve the issue. In your solution you say to copy a formula in cell H3 and drag down the rest of the column. In the next step you say to copy a formula into cell H4 and drag down the rest of the column. There is no way to ensure the values in column J are unique as they are box counts, and if I'm not mistaken you have said nothing about what to put in column V. I'm sorry but this really doesn't help at all.

[#4]

Can you show your expected result according to the sheet Master?

Which columns are source and which are expected result?

[#5]

@rshenk3 

I think this is what you're looking to do.  The seeding is done with SCAN's initial value argument:

=LET(
    carton_numbering, LAMBDA(a, v,
        IF(v = "", TEXTAFTER(a, ",", -1, , , a), TEXTJOIN(",", , SEQUENCE(v, , a + 1)))
    ),
    SCAN(1, boxes, carton_numbering)
)

Edit: I've also simplified the formula in the "Sort" column.

[#6]

Hi and thank you for your response. This certainly appears to be a more elegant solution than the formulas I came up with, but I have two questions. First, I will be deleting unused rows, even potentially what is now row 2, from the spreadsheet after filling in the requested quantities in column R from the PO. Does this mean I would apply the formula to both column U and column H after I have deleted the rows? Second, it does not appear to solve the problem of column H starting with the number 2. Thank you for your help.

[#7]

@rshenk3 

First, I will be deleting unused rows, even potentially what is now row 2, from the spreadsheet after filling in the requested quantities in column R from the PO. Does this mean I would apply the formula to both column U and column H after I have deleted the rows?

Yes, if you delete row 2 (the row containing the two formulas) they would need to be re-applied.

Second, it does not appear to solve the problem of column H starting with the number 2.

Can you clarify what you're looking for here? In my workbook the numbering starts at 2:

[#8]

Yes, I need the "Box #'s" to start at 1.

[#9 · Accepted solution]

@rshenk3 
Like this?

If that's the case, then we change the initial value in SCAN from 1 to 0:

=LET(
    carton_numbering, LAMBDA(a, v,
        IF(
            v = "",
            TEXTAFTER(a, ",", -1, , , a),
            TEXTJOIN(",", , SEQUENCE(v, , a + 1))
        )
    ),
    SCAN(0, boxes, carton_numbering)
)

[#10]

Yes, that's it! Thank you so much for your help! Your solution is much more efficient and I will take what I learned here to future projects. thanks again!

[#11]

Glad to help. Good luck with the business!