A macro for grouping of same values of several columns and totals

Hi Experts,

I'm newbie out here. I need your help with my problem on how to group of same values from several columns and summing the currency value. I’m using Excel 2003. I don’t know how to explain it. Let me illustrate an example:

Sheet1
A B C D E F G
1 Name ID No Code Nature Amount Rate Payable
2 Co. B 11-07 W1-02 CodeA 1,000 5% 50
3 Co. C W2-03 CodeB 2,000 7% 140
4 Co. A 11-08 W3-04 CodeC 3,000 9% 270
5 Co. B 11-07 W1-02 CodeA 4,000 5% 200
6 Co. A 11-08 W4-05 CodeD 5,000 10% 500
7 Co. C W2-04 CodeE 6,000 2% 120

The result should be like this:
Summary Report
A B C D E F G H
1 Seq.No. Name ID No. Code Nature Amount Rate Payable
2 1 Co. A 11-08 W3-04 CodeC 3,000 9% 270
3 2 Co. A 11-08 W4-05 CodeD 5,000 10% 500
4 3 Co. B 11-07 W1-02 CodeA 5,000 5% 250
5 4 Co. C W2-03 CodeB 2,000 7% 140
6 5 Co. C W2-04 CodeE 6,000 2% 120

Row 1: The headers

Column A: Seq. No. (Sequence number) – this is automatically numbered from 1 until the last value in Column B or Name Column.

Column B: Groups or merges the same value except if Column D has different value. In the sample result above, Co. A can’t merge because it differs in Codes (Column D).

Column F: Sums the same values in Column B and/or Column D

Column G: Rates are linked to Code (I guess just simply copy based on data on Sheet1)

Column H: Payable = Amount x Rate

This seems to be very complicated and I know someone out there willing to help me. I need a macro to create new sheet or a report and pivot is not suited for my project. I’ve seen somewhere in this forum about grouping of values but not the same case as mine. Please see attached file. Thanks in advance.

novice_excel_2012,

Welcome to the Excel Forum.


Detach/open workbook CreateSummaryReport - w1 wS - novice_excel_2012 - EF835236 - SDG10.xls and run the CreateSummaryReport macro.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the CreateSummaryReport macro.

novice_excel_2012,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.