Forum Discussion

wanalearn's avatar
wanalearn
Brass Contributor
Feb 14, 2023

formula or function How to sort a payroll report

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  s...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 14, 2023

    wanalearn 

    I think mtarler 's solution from the other discussion is a good one.  If length is an issue, you could fold the formula into a 'Payroll' Lambda where only the table or range is needed:

     

     

    =Payroll(Table1)

     

     

     

    If this works for you, please give credit to Matt for that formula from September.

    • wanalearn's avatar
      wanalearn
      Brass Contributor
      Feb 14, 2023
      can you explain how to fold it in a Payroll Lambada
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Feb 14, 2023

        wanalearn 

         

        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.

         

Resources