Forum Discussion

commander31's avatar
commander31
Copper Contributor
May 27, 2024

Formatting order numbers

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, s...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 27, 2024

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

 

commander31's avatar
commander31
Copper Contributor
May 28, 2024

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.

 
 
 
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 28, 2024

    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.

Resources