Forum Discussion

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

peiyezhu

Bronze Contributor

Apr 25, 2024

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;