Hindu has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Excel 2010
Hi,
I have a report that I export to excel from our payroll system. For example Pension Report. Unfortunately this report comes with subtotals already in the report. Is there a way I can get rid of the subtotals. Its hard to explain unless I send you the file for you to see.
Basically the report comes like this. All I want from the report is the subtotal.
For example EE 22 total ER is £630.54
EE56 total ER is £1128.21. How can I best achieve this?
Number Name Year Prd Type Input val Calc amount
22 xxxxxxx 2013/2014 9 Ers 70.06 70.06
2013/2014 8 Ers 70.06 70.06
2013/2014 7 Ers 70.06 70.06
2013/2014 6 Ers 70.06 70.06
2013/2014 5 Ers 70.06 70.06
2013/2014 4 Ers 70.06 70.06
2013/2014 3 Ers 70.06 70.06
2013/2014 2 Ers 70.06 70.06
2013/2014 1 Ers 70.06 70.06
Totals 630.54 630.54
56 xxxxxxx 2013/2014 9 Ers 124.29 124.29
2013/2014 8 Ers 124.29 124.29
2013/2014 7 Ers 124.29 124.29
2013/2014 6 Ers 124.29 124.29
2013/2014 5 Ers 124.29 124.29
2013/2014 4 Ers 124.29 124.29
2013/2014 3 Ers 124.29 124.29
2013/2014 2 Ers 137.09 137.09
2013/2014 1 Ers 121.09 121.09
Totals 1128.21 1128.21
66 xxxxxxx 2013/2014 9 Ers 118.26 118.26
2013/2014 8 Ers 118.26 118.26
2013/2014 7 Ers 118.26 118.26
2013/2014 6 Ers 118.26 118.26
2013/2014 5 Ers 118.26 118.26
2013/2014 4 Ers 118.26 118.26
2013/2014 3 Ers 118.26 118.26
2013/2014 2 Ers 130.36 130.36
2013/2014 1 Ers 115.24 115.24
Totals 1073.42 1073.42
Regards
Hindu
RE: Excel 2010
Hi Hindu
If I understand you correctly you are trying to hide certain rows of your report.
I believe a relatively straightforward way to achieve this would be to manually group the data as outlined below.
1. Select the row(s) which are not required to display. (You can select non-adjacent ranges by holding Ctrl as you select.)
2. In the ribbon area, from the Data tab > Outline choose Group > Group.
3. The - and + signs which appear next to the selected rows can then be used to collapse or expand sections of data as required.
4. The 1 2 3 buttons can be used to switch between different levels of detail within the data.
I hope this answers your question. Please let me know if you need any further clarification.
Kind regards
David
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
RE: Excel 2010
Dear David,
No this does not answer my question.
1) There are over 1,000 entries, so cant go and select them manually as will take took long
2) I get Error message “ The command you chose cannot perform multiple selections - Select single range and lick the command again.
Even it did work it wont help me as I would like my finish report to be like this
Number Name Total
22 Smith John 630.54
56 Ecclestone, A 1128.21
66 McDonald S 1073.42
but my report comes out like this
22 Smith John 2013/2014 9 Ers 70.06 70.06
2013/2014 8 Ers 70.06 70.06
2013/2014 7 Ers 70.06 70.06
2013/2014 6 Ers 70.06 70.06
2013/2014 5 Ers 70.06 70.06
2013/2014 4 Ers 70.06 70.06
2013/2014 3 Ers 70.06 70.06
2013/2014 2 Ers 70.06 70.06
2013/2014 1 Ers 70.06 70.06
Totals 630.54 630.54
for all the emplyees, for the payroll number and name is at the top but the totals of 630.54 is at the bottom. I cant do anything about how the system report comes out but I want to change it to the example I've given you above.
Regards
Hindu
RE: Excel 2010
Hi Hindu
I apologise if my suggestion was not appropriate - and you are right, it is not possible to outline multiple rows (I hadn't counted on that).
I need to discuss with one of my colleagues how we can best support you with this as without seeing the file it is quite difficult to know what to suggest.
I will aim to get back to you by 5pm on Friday.
Regards
David
RE: Excel 2010
Hi David,
Would you like me to send you a sample file. For you to have an idea at an email address.
Regards
Hindu
RE: Excel 2010
Hi Hindu
Thanks for getting in touch. If you send the file through to info@stl-training.co.uk I'll make sure it gets to David.
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
RE: Excel 2010
Hi Hindu
Just a quick note to keep you updated. I received the file which you sent late on Friday and am looking into it now.
I will update you once I have a proposed solution.
Kind regards
David
RE: Excel 2010
Hi Hindu
I believe I have found a potential solution using a macro. I will outline the steps I followed and have attached a file with macro attached.
Initially I deleted columns C-G as it appears these are not required.
I typed the word "Total" in cell C1
I then made Cell A2 the active cell.
On View > Macros I switched on "Use Relative References"
I clicked on "Record Macro" and gave it a name e.g. delete_rows and a keyboard shortcut e.g Ctrl +d
I then selected rows 3-10, right-clicked and chose Delete
I cut the contents of Cell B3 and pasted into Cell C2
I then selected rows 3 and 4 and right-clicked and chose Delete.
I then selected cell A3 to make it active cell.
I then stopped the macro recording.
At that point I saved the file as a macro-enabled workbook.
Once done selecting the number of the next employee down and running the macro (repeatedly) enables a user to quickly reformat all the other records in the same way.
I hope that this helps
Kind regards
David
Attached files...
RE: Excel 2010
Hi David,
Sorry how do I see the file that you've attached?
Regards
Hindu
RE: Excel 2010
Hi Hindu
I believe it should be available to download or open directly at the bottom of my last reply.
Kind regards
David
