Forum Discussion

Ronald1969's avatar
Ronald1969
Brass Contributor
Feb 24, 2024
Solved

Pull employee payroll details from multiple payrolls

  Hi!   I am trying to post this issue in different ways not going through, now trying for last time.   I have twelve wage sheets in one workbook. Each excel sheet is for a separate month - Janu...
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 24, 2024

    Ronald1969 

    To fully automate this sheet we need to restructure data, not sure right now how to do that better.

    As intermediate variant we may generate data for one employee block, ID will be the source. Plus rename sheets having everywhere 3-letterrs month name (June 2023 -> Jun 2023).

    With that into C1 we return the name

    =LET(
    Result, XLOOKUP(
        $H1,
        VSTACK('Jan 2023:Dec 2023'!$A$4:$A$100),
        VSTACK('Jan 2023:Dec 2023'!$B$4:$B$100) ),
    IFERROR(Result, "no or wrong data")
)

    Into B4 monthly data

    =LET(
    GetMonth, "'" & TEXT($A4, "mmm yyyy") & "'",
    IdRange, "!$A$4:$A$100",
    DataRange, "!$C$4:$L$100",
    Result, XLOOKUP(
        OFFSET($A4, -MONTH($A4)-2,7),
        INDIRECT(GetMonth & IdRange),
        INDIRECT(GetMonth & DataRange) ),
    IFERROR(Result, "no or wrong data")
)

    and drag it down to next months.

    After copy entire range A1:K16, paste into the A17, enter another Id into this block and repeat. All IDs are in O3.

Ronald1969's avatar
Ronald1969
Brass Contributor
Feb 24, 2024

SergeiBaklan 

 

Sir,

 

Please find attached a sample excel file, the last tap is "Individual Yearly Summary" I want the data extracted in that particular excel sheet.

 

Thanks for looking into my difficulty, look forward for your further support.  

 

Regards,

Ronald Pinto

peiyezhu's avatar
peiyezhu
Bronze Contributor
Feb 25, 2024

Ronald1969 

 

//select * from consolidateSheet limit 20;
create temp table aa as 
select * from consolidateSheet where regexp('^\d+',f03);
create temp table bb as 
select f03,f03 `Employ Code`,f04 `Employee Name`,f02 `Wage month`, colIdxf2[4:13]{%s %flds}{Basic	VDA	HRA	No of Days Paid	Basic2	VDA2	HRA2	Total Earned	Advance	Net Paid
} from aa;

select  f03||`Employee Name`||'<hr>'||udf_exec_sql('select colIndex[3:] from bb where f03 ='''||f03||'''') Result from bb group by f03 order by cast(f03 as int);

with sql.

 

 

 

 

Resources