Forum Discussion
formula or function How to sort a payroll report
I asked a similar question here
https://techcommunity.microsoft.com/t5/excel/is-there-a-formula-or-function/td-p/3616173
I got an answer from dscheikey to use =Filter () however it doesn't work here since I want it should stay on the same row just on a different column
I also got from mtarler it might work for me here, however, it is a long formula, I am sure there are more simple ones
- can you explain how to fold it in a Payroll Lambada
Take the original formula:
=LET(in,Table1, names,SCAN("",INDEX(in,,1),LAMBDA(prev,this,IF(this="",prev,this))), lines,SCAN(0,INDEX(in,,2),LAMBDA(prev,this,IF(this="Regular",prev+1,prev))), MAKEARRAY(MAX(lines),9,LAMBDA(r,c,IF(c<2,INDEX(FILTER(names,lines=r),1),IF(c<5,FILTER(INDEX(in,,c+1),(lines=r)*(INDEX(in,,2)="Regular")),IF(c<8,FILTER(INDEX(in,,c-2),(lines=r)*(INDEX(in,,2)="Overtime"),""),FILTER(INDEX(in,,2*c-13),(lines=r)*(INDEX(in,,2)="Total Earnings"),"")))))))
Store it in the name manager as 'Payroll'
=LAMBDA(table_or_range,LET(in,table_or_range, names,SCAN("",INDEX(in,,1),LAMBDA(prev,this,IF(this="",prev,this))), lines,SCAN(0,INDEX(in,,2),LAMBDA(prev,this,IF(this="Regular",prev+1,prev))), MAKEARRAY(MAX(lines),9,LAMBDA(r,c,IF(c<2,INDEX(FILTER(names,lines=r),1),IF(c<5,FILTER(INDEX(in,,c+1),(lines=r)*(INDEX(in,,2)="Regular")),IF(c<8,FILTER(INDEX(in,,c-2),(lines=r)*(INDEX(in,,2)="Overtime"),""),FILTER(INDEX(in,,2*c-13),(lines=r)*(INDEX(in,,2)="Total Earnings"),""))))))))
Then you may use Payroll at the sheet level. It's in the workbook I attached above.
- I am not so familiar with all these formulas
However, it looked like this formula is custom to this sheet. Is it true?
And if so which part? and how I can customize it for a similar situation in other words what is the syntax and what not