formula or function How to sort a payroll report

Question

I have imported a payroll report to Excell

However, the regular ''OT'' and Total are in the same column on other rows

I want a formula or function to put it on the same row in different columns

see attached what I did manually in column H I j

wanalearn · Answer

I&nbsp; asked a similar question herehttps://techcommunity.microsoft.com/t5/excel/is-there-a-formula-or-function/td-p/3616173&nbsp;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 columnI also got from mtarler it might work for me here, however, it is a long formula, I am sure there are more simple ones&nbsp;

patrick2788 · Answer

wanalearn&nbsp;I think&nbsp;mtarler&nbsp;'s solution from the other discussion is a good one.&nbsp; If length is an issue, you could fold the formula into a 'Payroll' Lambda where only the table or range is needed:&nbsp;&nbsp;=Payroll(Table1)&nbsp;&nbsp;&nbsp;If this works for you, please give credit to Matt for that formula from September.

wanalearn · Answer

can you explain how to fold it in a Payroll Lambada

patrick2788 · Answer

wanalearn&nbsp;&nbsp;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&lt;2,INDEX(FILTER(names,lines=r),1),IF(c&lt;5,FILTER(INDEX(in,,c+1),(lines=r)*(INDEX(in,,2)="Regular")),IF(c&lt;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"),"")))))))&nbsp;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&lt;2,INDEX(FILTER(names,lines=r),1),IF(c&lt;5,FILTER(INDEX(in,,c+1),(lines=r)*(INDEX(in,,2)="Regular")),IF(c&lt;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"),""))))))))&nbsp;Then you may use Payroll at the sheet level. It's in the workbook I attached above.&nbsp;

wanalearn · Answer

I am not so familiar with all these formulasHowever, 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

Forum Discussion

formula or function How to sort a payroll report

Share

Resources