i want to make 6 number id code with array formula by combining the job code and the number list... 2 first number is the job code and 4 last number is the number list..

I tried to divide the job code and the number list and then combine it at another column..

code - job 01 - manager 02 - supervisor 03 - staff

for the job code column, I used this =ARRAYFORMULA(IF(ROW(T:T)=1,"Job Code",ARRAYFORMULA(IFNA(vlookup((T:T),data!$A$2:$B$4,2,0)))))

for the number list, i used this =arrayformula({"Number List";iferror(vlookup(if(T2:T<>"",row(T2:T),),{filter(if(T2:T<>"",row(T2:T),),if(T2:T<>"",row(T2:T),)<>""),sequence(count(if(T2:T<>"",row(T2:T),)),1)},2,0),)})

and then I combine it with this formula =ARRAYFORMULA(IF(ROW(T:T)=1,"Kode ID",U1:U & V1:V))

PROBLEM Then I found a problem, that the number list is not what I required...

the result is 011 for the first manager applied... while what I expected is 010001..

and I tried to insert separate zeroes... but it only works for 1-9 😂...

for example when the number list extend to 10 or more... I want it to still be a 6 digits ID code... but what I get was 0100010 (7 digits)

How can I make an array formula for 6 digits id code??

  • Please edit your question to share sample data and formulas using table Markdown format or a link to a shared workbook. If you'd like to be more private use Blank sheet maker created by members of Google products experts program.
    – Blindspots
    Jan 24 at 18:04


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.