Feb 14 2023 11:12 AM - edited Feb 14 2023 11:33 AM
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
Feb 14 2023 11:29 AM - edited Feb 14 2023 11:59 AM
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
Feb 14 2023 11:50 AM - edited Feb 14 2023 11:56 AM
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.
Feb 14 2023 12:12 PM
Feb 14 2023 12:39 PM
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.
Feb 14 2023 01:40 PM
Feb 15 2023 02:46 PM
Feb 21 2023 08:52 AM
thanks for your answer I will respond to you on the other discussion where I want to clarify my questions