picking up same info from different columns

[rickf19]

Hi

I have a spreadsheet that comes every month with a lot of payroll data on it. I need to update a master sheet that holds summary details each month.
My problem is that the data in the columns from the payroll spreadsheet can be different each month eg month 1 col D may have OT1 but next month because no one has had OT1 col D may be hol pay, now 0t1 and ot2 are combined in the summary but holiday pay is added to salary,which can also move on the payroll spreadsheet, in the summary.
my question is, is there a way to pull the info into the correct summary column on the master sheet every month. Currently use a vlookup array but have to edit it each month to include the correct columns from the payroll spreadsheet.
The column headings are always the same just in different places on the payroll spreadsheet. eg
month 1
col D has ot1,col E has ot2, col F has Hol Pay, Col G has Bonus, Col H has Salary
month 2
Col D has Hol Pay col E has Sick Pay Col F has Bonus Col H has Redundancy Col I has Salary
month 3
Col D has ot2 Col E has Hol Pay Col F has Salary

I need on my master sheet to combine ot1 and ot2 in Column C , I need Hol pay , Bonus and Salary summed in column G, Sick Pay in Column D and Redundancy in column H.

Hope this is clear

Thanks
Rick

 

[sandy666]

post a link to the shared Excel files with representative examples of your sources and expected result.
use OneDrive, GoogleDrive or any similar

 

[rickf19]

Here is example of payroll file headings


[TABLE="width: 7702"]
<colgroup><col span="25"><col><col span="7"><col><col span="5"><col span="3"><col></colgroup><tbody>[TR]
[TD]AEGON EE [/TD]
[TD]Allowance 1 Car [/TD]
[TD]Annual Allowance [/TD]
[TD]Backdated Salary [/TD]
[TD]Basic Pay[/TD]
[TD]Charity [/TD]
[TD]Flex Bike To Work [/TD]
[TD]Flex Cash Plan [/TD]
[TD]Flex Childcare Vouchers [/TD]
[TD]Flex Gym Cost [/TD]
[TD]Flex Holiday Buy [/TD]
[TD]Flex Private Medical Cost [/TD]
[TD]Holiday Pay [/TD]
[TD]Homeworker Allowance [/TD]
[TD]KIT Day [/TD]
[TD]Lay Member Fees [/TD]
[TD]Overtime 2.0 [/TD]
[TD]Overtime Basic [/TD]
[TD]Pay In Lieu Of Notice [/TD]
[TD]Pension Allowance [/TD]
[TD]Redundancy Pay [/TD]
[TD]Salary Adjustment [/TD]
[TD]SMP[/TD]
[TD]SSP[/TD]
[TD]SSP Offset [/TD]
[TD][/TD]
[TD]Capital Credit Union [/TD]
[TD]Employees National Insurance[/TD]
[TD]Loan Repayments [/TD]
[TD]Recovery Of Advance [/TD]
[TD]Retail Vouchers [/TD]
[TD]Student Loan [/TD]
[TD]Tax[/TD]
[TD][/TD]
[TD]Additional Pension
Payable[/TD]
[TD]Employer Pension[/TD]
[TD]Employers National Insurance[/TD]
[TD]Er Flex Cash Plan Cost [/TD]
[TD]Er Flex Childcare Vouchers Cost [/TD]
[TD][/TD]
[TD]Net Pay[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-141.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4726.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4584.27[/TD]
[TD][/TD]
[TD="align: right"]422.10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]966.46[/TD]
[TD="align: right"]1388.56[/TD]
[TD="align: right"]141.78[/TD]
[TD="align: right"]425.34[/TD]
[TD="align: right"]533.40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1100.52[/TD]
[TD="align: right"]3195.71[/TD]
[TD="align: right"]3195.71[/TD]
[TD="align: right"]10269.06[/TD]
[/TR]
[TR]
[TD="align: right"]-878.86[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4882.58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-24.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3979.55[/TD]
[TD][/TD]
[TD="align: right"]391.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]664.70[/TD]
[TD="align: right"]1055.97[/TD]
[TD="align: right"]878.86[/TD]
[TD="align: right"]439.43[/TD]
[TD="align: right"]449.95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1768.24[/TD]
[TD="align: right"]2923.58[/TD]
[TD="align: right"]2923.58[/TD]
[TD="align: right"]9727.34[/TD]
[/TR]
[TR]
[TD="align: right"]-280.82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4011.73[/TD]
[TD][/TD]
[TD="align: right"]-30.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3700.91[/TD]
[TD][/TD]
[TD="align: right"]357.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]574.09[/TD]
[TD="align: right"]931.92[/TD]
[TD="align: right"]280.82[/TD]
[TD="align: right"]361.06[/TD]
[TD="align: right"]411.50[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1053.38[/TD]
[TD="align: right"]2768.99[/TD]
[TD="align: right"]2768.99[/TD]
[TD="align: right"]8455.20[/TD]
[/TR]
[TR]
[TD="align: right"]-92.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3069.18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-243.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2734.10[/TD]
[TD][/TD]
[TD="align: right"]241.81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]104.00[/TD]
[TD="align: right"]352.06[/TD]
[TD="align: right"]697.87[/TD]
[TD="align: right"]92.08[/TD]
[TD="align: right"]276.23[/TD]
[TD="align: right"]278.08[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]646.39[/TD]
[TD="align: right"]2036.23[/TD]
[TD="align: right"]2036.23[/TD]
[TD="align: right"]6114.59[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]-112.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3744.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3632.33[/TD]
[TD][/TD]
[TD="align: right"]349.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]561.79[/TD]
[TD="align: right"]911.39[/TD]
[TD="align: right"]112.34[/TD]
[TD="align: right"]337.02[/TD]
[TD="align: right"]402.04[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]851.40[/TD]
[TD="align: right"]2720.94[/TD]
[TD="align: right"]2720.94[/TD]
[TD="align: right"]8116.06[/TD]
[/TR]
[TR]
[TD="align: right"]-594.28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4244.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3650.55[/TD]
[TD][/TD]
[TD="align: right"]351.79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]543.75[/TD]
[TD="align: right"]895.54[/TD]
[TD="align: right"]594.28[/TD]
[TD="align: right"]382.03[/TD]
[TD="align: right"]404.55[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1380.86[/TD]
[TD="align: right"]2755.01[/TD]
[TD="align: right"]2755.01[/TD]
[TD="align: right"]8681.96[/TD]
[/TR]
[TR]
[TD="align: right"]-91.71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3057.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2965.29[/TD]
[TD][/TD]
[TD="align: right"]269.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]391.75[/TD]
[TD="align: right"]661.30[/TD]
[TD="align: right"]91.71[/TD]
[TD="align: right"]275.13[/TD]
[TD="align: right"]309.99[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676.83[/TD]
[TD="align: right"]2303.99[/TD]
[TD="align: right"]2303.99[/TD]
[TD="align: right"]6607.41[/TD]
[/TR]
[TR]
[TD="align: right"]-150.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2741.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2590.67[/TD]
[TD][/TD]
[TD="align: right"]224.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]91.00[/TD]
[TD="align: right"]314.05[/TD]
[TD="align: right"]629.65[/TD]
[TD="align: right"]150.78[/TD]
[TD="align: right"]246.73[/TD]
[TD="align: right"]258.29[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]655.80[/TD]
[TD="align: right"]1961.02[/TD]
[TD="align: right"]1961.02[/TD]
[TD="align: right"]5837.14[/TD]
[/TR]
[TR]
[TD="align: right"]-219.18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3652.92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-42.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3391.59[/TD]
[TD][/TD]
[TD="align: right"]320.71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]163.00[/TD]
[TD="align: right"]482.26[/TD]
[TD="align: right"]965.97[/TD]
[TD="align: right"]219.18[/TD]
[TD="align: right"]328.76[/TD]
[TD="align: right"]368.82[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]916.76[/TD]
[TD="align: right"]2425.62[/TD]
[TD="align: right"]2425.62[/TD]
[TD="align: right"]7699.94[/TD]
[/TR]
[TR]
[TD="align: right"]-114.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3821.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-40.22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3666.39[/TD]
[TD][/TD]
[TD="align: right"]353.69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]187.00[/TD]
[TD="align: right"]545.39[/TD]
[TD="align: right"]1086.08[/TD]
[TD="align: right"]114.64[/TD]
[TD="align: right"]343.91[/TD]
[TD="align: right"]406.74[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]865.29[/TD]
[TD="align: right"]2580.31[/TD]
[TD="align: right"]2580.31[/TD]
[TD="align: right"]8198.07[/TD]
[/TR]
</tbody>[/TABLE]
each row represents an employee
column headers in green are what moves month on month.

here
is current lookup that we use to "sort" the data on the summary sheet from the sheet above
{=SUM(IFERROR(VLOOKUP($D207,Jan!$E$6:$AL$250,{4,7,8,9,18,25,26,28,29},FALSE),0.01))}
D207 is the ees no

hope this clarifies a bit

Rick

 

[sandy666]

you posted something that shows nothing, maybe you know what and where are proper data and places but I don't

 

[rickf19]

If Aegon EE IS COL D , Allowance car1 is Col E Annual Allowance Col f Backdated Salary is Col G and Basic Pay is col H in this month
next month could be Additional Pay Col D Aegon ee Col E ot1 Col f Ot 2 Col g Allowance Car 1 Col H Backdated Pay Col I Basic Pay col J

So summary sheet groups info by employee and sums eg cols e,f,g and h as salary in month 1 next month needs cols h,i,j for salary and cols e and f for overtime.
Summary sheet column headings dont change position
any clearer

Rick

 

[sandy666]

use PowerQuery, sort columns of sources in the same order, use Append feature, group by employee with sum option

have a nice day

 

[rickf19]

Thanks
will investigate if I have access to power query