Forum Discussion

rshenk3's avatar
rshenk3
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...
excel
Formulas and Functions
office 365
  • Patrick2788's avatar
    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)
)

     

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 06, 2023

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.

 

rshenk3's avatar
rshenk3
Copper Contributor
Oct 07, 2023

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.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 07, 2023

    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:

     

    • rshenk3's avatar
      rshenk3
      Copper Contributor
      Oct 07, 2023
      Yes, I need the "Box #'s" to start at 1.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        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)
)

         

         

Resources