Copper Contributor

Oct 05, 2023

Solved

Automatic Carton Numbering

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 cust...

Apparel Master List Help.xlsx46 KB

excel

Formulas and Functions

office 365

Patrick2788

Oct 07, 2023

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

SCAN 10.7.xlsx50 KB

LeonPavesic

Silver Contributor

Oct 05, 2023

Hi rshenk3,

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

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

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)

rshenk3

Copper Contributor

Oct 05, 2023

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.

peiyezhu
Bronze Contributor
Oct 05, 2023
Can you show your expected result according to the sheet Master?

Which columns are source and which are expected result?

Forum Discussion

Automatic Carton Numbering

Resources