Forum Discussion

Joh_Loh's avatar
Joh_Loh
Copper Contributor
Apr 24, 2024

How to create an autonumbering formula based on three columns

Update:  Black cat on Stack Overflow gave the following formula that worked great: =MATCH(A2,UNIQUE(A$2:A2),0)&"."&MATCH(B2,UNIQUE(FILTER(B$2:B2,A2=A$2:A2)),0)&"."&MATCH(C2,UNIQUE(FILTER($C$2:$C2,A$...
excel
Formulas and Functions
Joh_Loh's avatar
Joh_Loh
Copper Contributor
Apr 25, 2024
Thanks for the reply, I went with a formula that I added to the original post
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 26, 2024

Joh_Loh 

Just for the sake of it, I want through your original formula from Black cat and refactored to use the 365 Advanced Formula Editor

 

The worksheet formula for the list of codes is 

 

= BYROW(specifications, GenerateCodeλ)

 

 where the Lambda function is defined by

 

/*  FUNCTION NAME:  GenerateCodeλ
    DESCRIPTION:    Assigns code numbers to a vehicle based upon its attributes within list */
/*  REVISIONS:      Date            Developer           Description
                    25 Apr 2024     Black cat           Original development on Stack Overflow 
                    26 Apr 2024     Peter Bartholomew   Refactored to use 365 methods
*/

GenerateCodeλ = LAMBDA(
// Parameter Declarations    
    vehicle,  //Record from specifications table corresponding to a specific vehicle
// Procedure
    LET(
//  Extract fields from specifications table
        make,  TAKE(specifications, , 1),  
        model, DROP(TAKE(specifications, , 2), , 1),
        color, TAKE(specifications, , -1),
//  Use range intersection to extract data for specific vehicle    
        vehicleMake,  (vehicle make),
        vehicleModel, (vehicle model),
        vehicleColor, (vehicle color),
//  Use XMATCH to assign distinct value to each property of specific vehicle
        makeNum,  XMATCH(vehicleMake, UNIQUE(make)),
        modelNum, XMATCH(vehicleModel, UNIQUE(FILTER(model, make = vehicleMake))),
        colorNum, XMATCH(vehicleColor, UNIQUE(FILTER(color, (make = vehicleMake) * (model = vehicleModel)))),
        vehicleCode, TEXTJOIN(".",,makeNum, modelNum, colorNum),
        vehicleCode
    )
)

 

This, most likely, is not what you expect to see from an Excel formula?

Resources