Macro to obtain subtotal of unique combinason

**msls09** · 02-08-2013, 03:26 AM

Hi,

Again I would need some help to try and facilitate a very lengthy task.

Attached is a my data. From column A to D.

I need to get the subtotal for the column C (Total) and D (Qty) for all unique combination of columns A and B. Moreover, I would need it in the format given from column G.

Note that:
1. This is only a very small sample. I can have up to around 20 different Ref in column A.
2. The type under each Ref (after running the macro) should be in ascending order.
3. Only existing type under each Ref should be included.

Well, I started doing the filter option but its too lengthy. I have also tried the subtotal option but again my data is too long. Please help.

**mrice** · 02-08-2013, 04:25 AM

Try this macro

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**visha_1984** · 02-08-2013, 04:48 AM

Hi
msls

u can use pivot table for easy way i have attached it pls check

**OllieB** · 02-08-2013, 04:54 AM

Try the attached workbook. Note the results are written to Sheet2. I think the result data provided by you is not entirely correct. For example I do not see an aggregated total & quantity for A P134004 while the combination does exist in the data, I am therefore assuming the results in column G are shown for the desired layout only.

**msls09** · 02-15-2013, 01:26 AM

Dear OllieB and mrice,

I apologize for the late reply but I was on holidays for a week. Kindly note that I am still unable to run it. Both your macros work just fine on Excel 2007 but bugs when it comes to Excel 2003 (which most users have here!).

I had a look at mrice's macro - and I believe that it would be the sumifs function (which is not possible in MS 2003). As for OllieB, it seems that MS Excel 2003 does not run the "SortOn:=xlSortOnValues" function.

Kindly help.

msls09

**msls09** · 02-15-2013, 01:48 AM

Dear mrice,

I apologize for the late reply but I was on holidays for a week. Kindly note that I am still unable to run it. Both your macros work just fine on Excel 2007 but bugs when it comes to Excel 2003 (which most users have here!).

I had a look at your macro - and I believe that it would be the sumifs function (which is not possible in MS 2003). It also seems that MS Excel 2003 does not run the "SortOn:=xlSortOnValues" function.

Kindly help.

msls09

**nilem** · 02-15-2013, 02:19 AM

Hi msls09,
try it

Please Login or Register  to view this content.

**OllieB** · 02-15-2013, 02:36 AM

Originally Posted by msls09

Dear OllieB and mrice,

I apologize for the late reply but I was on holidays for a week. Kindly note that I am still unable to run it. Both your macros work just fine on Excel 2007 but bugs when it comes to Excel 2003 (which most users have here!).

I had a look at mrice's macro - and I believe that it would be the sumifs function (which is not possible in MS 2003). As for OllieB, it seems that MS Excel 2003 does not run the "SortOn:=xlSortOnValues" function.

Kindly help.

msls09

The "SortOn:=xlSortOnValues" is present in both solutions, so if it works in the solution of mrice, it cannot be the problem in mine.