VBA for Excel sheets

Question

I have an excel sheet in which records of employee's and their various earnings and deductions like below:

Emp Name    Earning Code    Earning    Deduction Code    Deductions
John        Regular         5000       PF                500
John        Overtime        750        income Tax        250
John        Bonus           750        Loan 1            250
John                                   Loan 2            250
Peter       Regular         6000       PF                600
Peter       Overtime        850        income Tax        250
Peter       Bonus           650        Loan 1            250
Peter                                  Loan 2            250

I need to make another sheet in the same Excel with following format:

Employee Name    Total Earning    Total Deduction    Net Income
John             6500             1250               5250
Peter            7500             1350               6150

I am not that strong in Excel, so I am seeking any kind of help in achieving this objective. Any help would be highly appreciated!!

Thanks!

Answer 1

For a pure excel solution you can use Subtotal in Data Tab.

1. Select all your data. Go to Data Tab and click on subtotal.

2. Select your earning and Deduction column from checkbox like this

3. To copy only the subtotals collapse the level 2 on clicking

4. Select the data. Press F5 select Visible cells only from the dialog.

5. Press Ctrl+C
6. Go to your target to paste and do Ctrl+V in new sheet in your case.