Pull employee payroll details from multiple payrolls

[Ronald1969]

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

[#2]

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

[#3]

@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

[#4 · Accepted solution]

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

[#5]

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

[#6]

@SergeiBaklan 

Sir,

I copied the formula which you had provided by making some changes in identification of column and row range. I didn’t have issue with the first formula but with second one I have an issue.

In Cell C1 I copied the formula and name correctly returns, No issue with this below formula

=LET(Result,XLOOKUP($H1,VSTACK('JAN 2023:DEC 2023'!$B$5:$B$100),VSTACK('JAN 2023:DEC 2023'!$C$5:$C$100)),IFERROR(Result,"No Data") )

In Cell B5 I copied the formula and made required changes in Column name and number. The result is incorrect, I am not getting the details of employee, instead I am getting the total sums below each column. I have made the following changes

=LET( GetMonth, "'" & TEXT($A5, "mmm yyyy") & "'", IdRange, "!$B$5:$B$100", DataRange, "!$F$5:$V$100", Result, XLOOKUP( OFFSET($A5, -MONTH($A5)-2,7), INDIRECT(GetMonth & IdRange), INDIRECT(GetMonth & DataRange) ), IFERROR(Result, "Nil") )

The Excel sheet where I have copied this formula has to capture from 45 names (rows) in each monthly sheet. The Column used in the monthly sheet are from A to V and the data to be captured begins at Column F5 and goes upto Column V5 and below rows up to F45 to V45. In-between that in Column G5 to G45 is a remark column consisting of “Text”.

Can you please help to correct the formula.

Ronald

[#7]

@Ronald1969 

Perhaps that's due to that part of the formula

OFFSET($A4, -MONTH($A4)-2,7)

For the sample file in previous post this part of formula for the Jan (cell B4) takes the value from the cell which is located on 3 ( -MONTH(A4)-2 = -1-2=-3) rows up from A4 and on 7 columns to the right. Other words value from the cell H1 which shall be 26 in the sample.

For the other data layout you shall adjust OFFSET accordingly.

[#8]

Yes Sir, your advice is correct. I applied it and the result is as desired.

Thanks for making my work life easy, you helped me save valuable time.

Thanks once again,

Ronald

[#9]

@Ronald1969 

You are welcome, glad to help