formula or function How to sort a payroll report

[wanalearn]

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

[#2]

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

[#3]

@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.

[#4]

can you explain how to fold it in a Payroll Lambada

[#5]

@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.

[#6]

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

[#7]

so "custom to this sheet" yes the LAMBDA is define in that workbook and therefore can only be used in this workbook unless you copy it/define it in another workbook. This LAMBDA uses only 'standard' worksheet formulas and a couple of items from the sheet. So anything that is all CAPS is a worksheet function and it appears that all of the lowercase items are internally defined variables and the only 'sheet specific' items are things inside quotes "". So things like:
"Regular" and "Overtime" and "Total Earnings". It also has 'sheet specific' values for expecting things in a certain order like INDEX(in,,1) is used to look at the first column and expects that column to be the names. I don't know if that answer you questions or how to make it most flexible for you.

[#8]

@mtarler 

thanks for your answer I will respond to you on the other discussion where I want to clarify my questions