How to create an autonumbering formula based on three columns

[Joh_Loh]

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$2:A2&B$2:B2=A2&B2)),0)

Hi All, I'm trying to create a formula that will automatically generate a numeric code for each combination of columns. The first column is populated by a dropdown list, so that's an easy if-then formula (make in the attached image). The hard part is assigning column B a value that restarts when there is a new "make", and yet assigns the same value if that make is already entered. And then tougher yet is to assign a colour code that also restarts when there is a new model.

So, in the table below in column A, users pick their make. Honda will auto populate "1." into column D. Then the user enters the make into column B. Row 2 will add "1." into the code for the first entry (Accord), "2." for the second entry, etc. But if one of those models gets entered again in a lower row, the code will remember what was already assigned.

Same idea for column D.

I can't make only drop-down lists for the model and colour column - users need to be able to enter custom values (it's not for make-model-colour - I'm just using that as an example to show what I need).

I've tried using countif and if-then statements using arrays, but it's not working. Any solutions would be greatly appreciated.

A	B	C	D
Make	Model	Colour	Code
Honda	Accord	Black	1.1.1
Honda	Civic	Red	1.2.1
Toyota	Rav4	Silver	2.1.1
Honda	Accord	Blue	1.1.2
Ford	F-150	Onyx	3.1.1
Ford	F-150	White	3.1.2
Chevrolet	Silverado	Moonlight	4.1.1
Ford	F-150	Steel	3.1.3
Chevrolet	Silverado	Pearl	4.1.2
Audi	Q4	Midnight	5.1.1
Audi	Q4	Chrome	5.1.2
Audi	Q8	Night	5.2.1
Audi	Q4	Gunmetal	5.1.3

[#2]

@Joh_Loh If your data is formatted as a structured Excel table, one possible calculated column formula could be:

=LET(
    ua, UNIQUE([Make]),
    ub, UNIQUE(Table1[[Make]:[Model]]),
    uc, UNIQUE(Table1[[Make]:[Colour]]),
    TEXTJOIN(".",,
        XMATCH([@Make], ua),
        XMATCH([@Model], FILTER(CHOOSECOLS(ub, 2), CHOOSECOLS(ub, 1)=[@Make])),
        XMATCH([@Colour], FILTER(CHOOSECOLS(uc, 3), (CHOOSECOLS(uc, 1)=[@Make])*(CHOOSECOLS(uc, 2)=[@Model])))
    )
)

As a dynamic array variant, with the MAP function:

=LET(
    ua, UNIQUE(A2:A14),
    ub, UNIQUE(A2:B14),
    uc, UNIQUE(A2:C14),
    MAP(A2:A14, B2:B14, C2:C14,
        LAMBDA(a,b,c,
            TEXTJOIN(".",,
                XMATCH(a, ua),
                XMATCH(b, FILTER(CHOOSECOLS(ub, 2), CHOOSECOLS(ub, 1)=a)),
                XMATCH(c, FILTER(CHOOSECOLS(uc, 3), (CHOOSECOLS(uc, 1)=a)*(CHOOSECOLS(uc, 2)=b)))
            )
        )
    )
)

See attached sample workbook, if necessary...

[#3]

=BYROW(MAP(A2:C14,LAMBDA(x,IF(COLUMN(x)=1,MATCH(x,UNIQUE(A2:A14),),"."&MATCH(x,UNIQUE(FILTER(OFFSET(x,2-ROW(x),):x,MMULT(--(A2:OFFSET(x,,-1)=OFFSET(x,,1-COLUMN(x),,COLUMN(x)-1)),SEQUENCE(COLUMN(x)-1)^0)=COLUMN(x)-1)),)))),CONCAT)

@Joh_Loh 

[#4]

@Joh_Loh 

SQL：

 create temp table aa as 

select row_number() over ( order by rowid) Code,null Parent,Make from autonumbering group by Make ;

create temp table bb as 

select row_number() over ( partition by Make order by rowid) Code,Make,Model from autonumbering group by Make,Model ;

create temp table cc as 

select row_number() over ( partition by Make,Model) Code,Make,Model,Colour from autonumbering order by rowid;

select Make,Model,Colour,(select aa.code from aa where aa.Make like cc.make)||'.'||(select code from bb where bb.Make||bb.Model like cc.make||cc.Model)||'.'||code Code from cc;

 

[#5]

@Joh_Loh 

Seems I made too much of a meal of this!  As well as generating a code that depends upon the sorted order as opposed to the original order.

This is mainly sorting and formatting

= LET(
    seq,       SEQUENCE(13),
    sorted,    SORT(HSTACK(spec,seq), {1,2,3}),
    autoNum,   SCAN(0, SEQUENCE(13), IncrementAutoNumλ),
    formatted, TEXT(autoNum, "0\,0\,0"),
    SORTBY(formatted, CHOOSECOLS(sorted, 4))
  )

The key is the Lambda function that identifies model changes and increments the code

IncrementAutoNumλ
=    LAMBDA(acc,k,
        IF(k=1,
            111,
            LET(
                changeMake?,  COUNTA(UNIQUE(INDEX(sorted, VSTACK(k, k-1), 1))) - 1,
                changeModel?, COUNTA(UNIQUE(INDEX(sorted, VSTACK(k, k-1), 2))) - 1,
                increment,    IFS(changeMake?, 100, changeModel?, 10, 1, 1),
                acc + increment
            )
        )
    )

Now I guess its time to look at the other solutions to determine how to get the result efficiently!

[#6]

Thanks for the formula, unfortunately, it gives me a #NAME? error and I can't seem to resolve it

[#7]

Thanks for the response, I went with a formula that I have added to the original post

[#8]

Thanks for the reply, I went with a formula that I added to the original post

[#9]

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