How to group

[Dave]

Hi,
I have a spreadsheet with the following columns:

Item # 1 to 1000
Account Number alphanumeric 20 characters long
Amount 1
Amount 2
Period 1
Period 2

Data is currently sorted by Amount 2 ( largest to smallest).

There are multiple entries for the same account.

What I want to do is now group the data so that All the same account
numbers are grouped together along with all other fields but in this
grouping I want the amounts to keep the current sort largest to
smallest ( largest to smallest).If an account as three numbers I want
them on the sheet in consecutive lines

Thanks for all your help,

 

[Wouter HM]

Hi Dave,

In Exel 2007 I recorded this macro for you:

Sub Dave()
'
' Dave Macro
'

' Select area with data
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

' Sort Acsending on account nd descending on Amount 2
ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add _
Key:=Range("B2:B81"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add _
Key:=Range("D281"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Blad1").Sort
.SetRange Range("A1:F81")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Group on Account with as bonus totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:=Array(3, 4), Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True
End Sub


HTH,

Wouter