How to create an autonumbering formula based on three columns
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
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. ABCDMakeModelColourCodeHondaAccordBlack1.1.1HondaCivicRed1.2.1ToyotaRav4Silver2.1.1HondaAccordBlue1.1.2FordF-150Onyx3.1.1FordF-150White3.1.2ChevroletSilveradoMoonlight4.1.1FordF-150Steel3.1.3ChevroletSilveradoPearl4.1.2AudiQ4Midnight5.1.1AudiQ4Chrome5.1.2AudiQ8Night5.2.1AudiQ4Gunmetal5.1.3 Read More