Sort Data and total grouped data

[detrw4always]

[h=2]Sort data by one column then total by using sub totals in that colomn[/h]

Here is my data:

Description Seller Number Buyer Number Sale Price Commission Sales Tax Buyers Preimum Total
Brown Riding Plush Horse 100 342 30.5 6.1 1.83 4.58 36.91
Blue Boys Mongoose bike 20" 200 200 55 9.9
Queen Purple Bed Comforter 100 342 85 12.75
Large Oak Bedroom Dresser 150 12


I would like a macro that can be run on the fly, to sort buy buyer number, then total by each buyer number it would be totaling the total column and the commission column. I do not write macros and know very very little about how to write them so I need a pretty detailed explaintion on how I can go about doing this. I would need to be able to run this through out a night at various points as an auction is taking place.

ANY IDEAS? I know it can be done just not sure how to do it on my own.

Thanks,​

 

[Peter_SSs]

Welcome to the MrExcel board!

It isn't clear just what columns are what, but see if you can make use of this.

Sub SortAndSubtotal()
  With Range("A1").CurrentRegion
    .Sort Key1:=.Columns(3), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  End With
End Sub

The red 3s should be whatever column the "Buyer Number" is. So 3, as I have it would be column C.

The blue numbers should be a list of the columns that you want the subtotals for. So 5, & 8 as i have it would be columns E & H.

 

[detrw4always]

Welcome to the MrExcel board!

It isn't clear just what columns are what, but see if you can make use of this.

Sub SortAndSubtotal()
  With Range("A1").CurrentRegion
    .Sort Key1:=.Columns(3), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  End With
End Sub

The red 3s should be whatever column the "Buyer Number" is. So 3, as I have it would be column C.

The blue numbers should be a list of the columns that you want the subtotals for. So 5, & 8 as i have it would be columns E & H.

"you are right I want to sort by column c and then add column i over all. As I have no idea how to go about doing a macro, if anyone has the ability to help me step by step as I tried this macro with my limited knowledge and it would not work." Thank you

 

[Peter_SSs]

you are right I want to sort by column c and then add column i over all.

In that case, the code would be

Sub SortAndSubtotal()
  With Range("A1").CurrentRegion
    .Sort Key1:=.Columns(3), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  End With
End Sub

..I tried this macro with my limited knowledge and it would not work.

Just saying something would not work, doesn't give us much to go to help.
How did you 'try' it?
Did it do nothing?
Did it give the wrong results?
Did it give an error message?
Something else

As I have no idea how to go about doing a macro, if anyone has the ability to help me step by step ..

There are several ways to implement & run a macro. Here is one way.

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window that opens, use the menu to Insert|Module

3. Copy and Paste the code above into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Back at your sheet, press Alt+F8, select the 'SortAndSubtotal' macro and click 'Run'

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabld workbook (*.xlsm)