Formatting order numbers

[commander31]

I need help with the ROW function: I am trying to set up a document to keep track of orders where each row represents one item in the order. some orders are for only one item, some are for several, so I need the "Order Number" column to format the order number as "24-[order number]" but only in cells where a new order is entered (the first row in a multi-item order). I can't figure out a formula that lets me both enter a sequence (automatically format as "24-0001" "24-0002" etc) and skip rows while keeping track of skipped rows. What I have now is =IF(ISBLANK(J2)," ",TEXT(ROW(A1),"24-0000")) - this keeps multi-item rows except the first blank but resumes the count as though the blank rows count.

[#2]

@commander31 

Do you have Excel 365?  If so

= LET(
    priorOrder, DROP(VSTACK("", order), -1),
    newOrder?,  order<>priorOrder,
    orderNum,   SCAN(0, newOrder?, LAMBDA(acc,new?, IF(new?, acc+1, acc))),
    itemNumber, SCAN(0, newOrder?, LAMBDA(acc,new?, IF(new?, 1, acc+1))),
    HSTACK(orderNum, itemNumber)
  )

will generate sequence numbers both for orders and items within each order.

It should also be easy to do the same thing with relative referencing but that is not anything I would use (my personal preference, not a recommendation).

[#3]

@PeterBartholomew1thank you but this seems overly complex and I'm not sure how to apply it to my current document. I've been trying to integrate the COUNTA function in the formula but it hasn't produced satisfactory results.

[#4]

@commander31 

It gets worse.  More technically demanding to write, that is.  The plus is that it gets easier to use in the workbook once developed.  For example, the Lambda function, 'OrderNumberλ could be used while never looking at its definition.

OrderNumberλ
=LAMBDA(order, [init],
    LET(
        ini, IF(ISOMITTED(init), 0, init-1),
        priorOrder, DROP(VSTACK("", order), -1),
        newOrder?, order <> priorOrder,
        orderNum, SCAN(
            ini,
            newOrder?,
            LAMBDA(acc, new?, IF(new?, acc + 1, acc))
        ),
        itemNumber, SCAN(
            0,
            newOrder?,
            LAMBDA(acc, new?, IF(new?, 1, acc + 1))
        ),
        orderNum & TEXT(itemNumber, "-0000")
    )
);

Whilst that might create an OMG moment, the worksheet formula

= OrderNumberλ(order, 24)

to create the sequence

should not prove impossible to use.