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 - January 2023 to December 2023 and consists of about 40 to 60 names in each wage sheet. many are not repeated employees; turnover of employees is more. 

 

I am tasked with creating individual excel sheet for each employee and each row should show the extract of a separate monthly payroll. (Example A1 to K1 will be the common heading, A2 to K2 will be details of January 2023, A3 to K3 details for February 2023 ... A4 to K4 for March 2023and so on)

 

Each employee is identified with an employee code number, running in four digits.

 

Can some help me in resolving my issue, I will be grateful.

 

regards,

 

Ronald Pinto

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.

8 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 24, 2024

    Ronald1969 

    It depends do you keep your data in ranges or in structured tables. If in ranges, you may first pickup employees ID as

    =SORT( UNIQUE(VSTACK(Jan:Dec!A1:A100)) )

    After that something like

    =XLOOKUP(A1#,Jan!A:A,Jan!AA:AA)

    assuming in column A is ID and in column AA is result for the given month.

    Above is just an idea, that could be bit more automated if to know what is your data structure exactly.

    • 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