98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel
Excel
Resolved · High Priority · Version 2010
Mon 16 Dec 2013, 14:41Delegate Hindu said...
Hindu has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Excel
Hi,
I am a Payroll and HR Administrator and am constantly asked to do reports that involves using both HR and Payroll tables. Is there a way I can join the two reports together without using look up for each column ( it gets labour intensive as there are many columns). For example from HR I can run report to give me Name, Surname, Date of joining, date of birth, age, department, line manager etc and on the other report I can get Gross Pay, Gross Taxable, NI, Tax, and all other pay elements. Is there a way of merging the two spreadsheet together to make one report.
Regards
Hindu
Tue 17 Dec 2013, 21:47Trainer Gary said...
RE: Excel
Hi Hindu
Thanks for getting in touch. I suspect a VLOOKUP would be the best way to bring these two lists together. If it's the quantities of columns and dragging across that's a problem, I can help with that. I just have to ask first, is there something you can look at to tie both sheets together, e.g. Employee ID?
Let me know and I can offer a suggestion. Otherwise we might have to go a different route. Also, it would be helpful to know what sort of analysis you're interested in performing.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 18 Dec 2013, 09:54Delegate Hindu said...
RE: Excel
Hi Gary,
There are sometimes more than one spreadsheet that I need to link up. V Looks takes ages and also there are some data that the return is 0 so I end up with a lot of N/A which I have to then clear up.
I have spreadsheet with different information on them for example the main one is the HR spreadsheet which will have all the employees in the company ( unique identifier is Employee ID) so the main spreadsheet will have all employees and their personal details eg Title, Date of Birth, Date they joined the Company, ages, length of service etc and the other spreadsheet will have all the other payroll data eg Gross Pay NI, Overtime, Bonus, Incentive, Attendance Allowance etc so I want to link them together. Example - One employee will be in the main spreadsheet as their are current but they might not have overtime for that month so when you do v look it will return as N/A and I will get a lot of N/A for different pay elements on different columns. Is it possible to merge/ link the spreasheets together? v lookup is not really an option for me as I have to many columns to link to the main spreadsheet.
Regards
Hindu
Regard
Wed 18 Dec 2013, 10:16
Edited on Wed 18 Dec 2013, 10:17Trainer Gary said...
RE: Excel
Hi Hindu
Thanks for clarifying. I'd like to offer an example of VLOOKUP that's very flexible and doesn't require a lot of setup. I've attached a file that gives you an example.
The key formula is this extended VLOOKUP:
=IFERROR(VLOOKUP($A2,Sheet2!$A$2:$F$17,MATCH(Sheet1!G$1,Sheet2!$A$1:$F$1,0),0),0)
The IFERROR part will hide your N/A messages. You usually do something like this:
=IFERROR(formula, error message)
In my example, if the VLOOKUP fails it returns a zero. This allows you to calculate with ease.
Then there's the VLOOKUP which I assume you are familiar with. However instead of specifying the column index number, I've used a MATCH function to figure out where the heading resides. It looks like this:
=MATCH(lookup value, table, match type)
If you copy the headings from one sheet into your "master sheet" this lookup will retrieve them with minimal fuss. Do note the absolute and mixed references in the formula.
This means the manual part will be a copy and paste, and creating the lookup formula. After that it will be a case of dragging the formula around.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
GF Advanced VLOOKUP.xlsx
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Select Single Data MarkerTo select a single data marker in a chart, ie line, bar or column; |
