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