picking up same info from different columns

rickf19

Board Regular
Joined
Aug 30, 2019
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
post a link to the shared Excel files with representative examples of your sources and expected result.
use OneDrive, GoogleDrive or any similar
 
Last edited:
Upvote 0
Here is example of payroll file headings


AEGON EE Allowance 1 Car Annual Allowance Backdated Salary Basic PayCharity Flex Bike To Work Flex Cash Plan Flex Childcare Vouchers Flex Gym Cost Flex Holiday Buy Flex Private Medical Cost Holiday Pay Homeworker Allowance KIT Day Lay Member Fees Overtime 2.0 Overtime Basic Pay In Lieu Of Notice Pension Allowance Redundancy Pay Salary Adjustment SMPSSPSSP Offset Capital Credit Union Employees National InsuranceLoan Repayments Recovery Of Advance Retail Vouchers Student Loan TaxAdditional Pension
Payable
Employer PensionEmployers National InsuranceEr Flex Cash Plan Cost Er Flex Childcare Vouchers Cost Net Pay
-141.784726.054584.27422.10966.461388.56141.78425.34533.401100.523195.713195.7110269.06
-878.864882.58-24.173979.55391.27664.701055.97878.86439.43449.951768.242923.582923.589727.34
-280.824011.73-30.003700.91357.83574.09931.92280.82361.06411.501053.382768.992768.998455.20
-92.083069.18-243.002734.10241.81104.00352.06697.8792.08276.23278.08646.392036.232036.236114.59
0.000.000.000.000.000.000.000.000.000.00
-112.343744.673632.33349.60561.79911.39112.34337.02402.04851.402720.942720.948116.06
-594.284244.833650.55351.79543.75895.54594.28382.03404.551380.862755.012755.018681.96
-91.713057.002965.29269.55391.75661.3091.71275.13309.99676.832303.992303.996607.41
-150.782741.452590.67224.6091.00314.05629.65150.78246.73258.29655.801961.021961.025837.14
-219.183652.92-42.153391.59320.71163.00482.26965.97219.18328.76368.82916.762425.622425.627699.94
-114.643821.25-40.223666.39353.69187.00545.391086.08114.64343.91406.74865.292580.312580.318198.07

<colgroup><col span="25"><col><col span="7"><col><col span="5"><col span="3"><col></colgroup><tbody>
</tbody>
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
 
Upvote 0
you posted something that shows nothing, maybe you know what and where are proper data and places but I don't
 
Upvote 0
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
 
Upvote 0
use PowerQuery, sort columns of sources in the same order, use Append feature, group by employee with sum option

have a nice day
 
Upvote 0
Thanks
will investigate if I have access to power query
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top