Oct 05 2023 03:08 AM
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!
Oct 05 2023 05:25 AM
Hi @rshenk3,
you can try to solve the problem of automatically numbering cartons in Excel with a dummy row by using the following steps:
=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))
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:
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)
Oct 05 2023 01:58 PM
Oct 05 2023 06:22 PM - edited Oct 05 2023 06:23 PM
Can you show your expected result according to the sheet Master?
Which columns are source and which are expected result?
Oct 06 2023 06:49 AM - edited Oct 06 2023 02:52 PM
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.
Oct 07 2023 01:38 AM - edited Oct 07 2023 01:40 AM
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.
Oct 07 2023 02:21 AM
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:
Oct 07 2023 04:27 AM - edited Oct 07 2023 04:49 AM
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)
)
Oct 07 2023 06:17 AM
Oct 07 2023 04:27 AM - edited Oct 07 2023 04:49 AM
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)
)