A worksheet is populated with several columns of sales data, starting at row 25. Above this is a summary area.

One of the columns is 'category' and another is 'sales'. There could be a range in number of categories as each time the report is run,it might be different. There will be a minimum of 1 category and maximum of say 20. Individual item sales are then listed in rows, with each item having a category assigned to it.

In the summary area I would like to list each category once, along with a subtotal all items for that category from the sales column.

To carry it one step further, I would like to list these categories in descending order based on the sales total.

Any ideas?

Thanks in advance,
Denny

Simple approach...

Sheet1 Cols A,B,C,D
Col A: Name it "Sheet1!Categories"; RefersTo: "=$A:$A"
Col C: Name it "Sheet1!Sales"; RefersTo: "=$C:$C"

Select D1 and open the Define Name dialog and enter the following...

Name: "Category"
Scope: Sheet1
RefersTo" "=B1"

In your summary area:
list all category names in Col B
In Col D enter this formula...
=SUMIF(Categories,Category,Sales)

You can select all rows in Col D and use Ctrl+Enter to put the formula
into all cells simultaneously, OR just enter it in 1 cell and copy
down.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, March 4, 2015 at 9:12:44 AM UTC-5, GS wrote:
Simple approach...

Sheet1 Cols A,B,C,D
Col A: Name it "Sheet1!Categories"; RefersTo: "=$A:$A"
Col C: Name it "Sheet1!Sales"; RefersTo: "=$C:$C"

Select D1 and open the Define Name dialog and enter the following...

Name: "Category"
Scope: Sheet1
RefersTo" "=B1"

In your summary area:
list all category names in Col B
In Col D enter this formula...
=SUMIF(Categories,Category,Sales)

You can select all rows in Col D and use Ctrl+Enter to put the formula
into all cells simultaneously, OR just enter it in 1 cell and copy
down.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks, Garry!
One thing I was not clear on is that I needed to list the categories in the summary area automatically. They can change completely each time I drop sales data into the report, in both variety and number. May be completely different categories each time, so I need to list each category once in the summary area, along with the subtotal for each category. Sorry for the confusion!
Denny

On Wednesday, March 4, 2015 at 9:12:44 AM UTC-5, GS wrote:
Simple approach...

Sheet1 Cols A,B,C,D
Col A: Name it "Sheet1!Categories"; RefersTo: "=$A:$A"
Col C: Name it "Sheet1!Sales"; RefersTo: "=$C:$C"

Select D1 and open the Define Name dialog and enter the following...

Name: "Category"
Scope: Sheet1
RefersTo" "=B1"

In your summary area:
list all category names in Col B
In Col D enter this formula...
=SUMIF(Categories,Category,Sales)

You can select all rows in Col D and use Ctrl+Enter to put the
formula into all cells simultaneously, OR just enter it in 1 cell
and copy down.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks, Garry!
One thing I was not clear on is that I needed to list the categories
in the summary area automatically. They can change completely each
time I drop sales data into the report, in both variety and number.
May be completely different categories each time, so I need to list
each category once in the summary area, along with the subtotal for
each category. Sorry for the confusion! Denny

So you're saying you want to 'dump' data starting at row 25 below the
summary area and have a 'unique' list of category names automatically
populate col B in the summary area?

Assuming you have done your layout as described above, this routine
does everything automatically...


Sub CreateCategoryList()
' Creates a unique list of names starting in B2
' of the summary area.

Dim n&, k&, lRow&, rngData As Range, sList$
Set rngData = ActiveSheet.UsedRange
lRow = Cells(Rows.Count, 1).End(xlUp).Row
k = 1 '//header row

rngData.Range("B2:D24").ClearContents
For n = 25 To lRow
If Not InStr(sList, rngData.Cells(n, 1)) 0 Then
'Add name to sList and increment counter 1 row
sList = sList & "," & rngData.Cells(n, 1): k = k + 1
With rngData.Cells(k, 2)
.Value = rngData.Cells(n, 1)
.Offset(0, 2).Formula = "=SUMIF(Categories,Category,Sales)"
End With
End If
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops.., forgot to include Sort...

Sub GetSummaryInfo()
' Creates a unique list of names starting in B2
' of the summary area.

Dim n&, k&, lRow&, sList$
Dim rngData As Range, rngSummary As Range
Const sSortField$ = "D2:D24" '//subtotal col

'Initialize vars
Set rngData = ActiveSheet.UsedRange
Set rngSummary = rngData.Range("B2:D24")
lRow = Cells(Rows.Count, 1).End(xlUp).Row
k = 1 '//header row

rngSummary.ClearContents
For n = 25 To lRow
If Not InStr(sList, rngData.Cells(n, 1)) 0 Then
'Add name to sList and increment counter 1 row
sList = sList & "," & rngData.Cells(n, 1): k = k + 1
With rngData.Cells(k, 2)
.Value = rngData.Cells(n, 1)
.Offset(0, 2).Formula = "=SUMIF(Categories,Category,Sales)"
End With
End If
Next 'n

SortData sSortField, rngSummary.Address, xlDescending
End Sub

Sub SortData(sKey$, sSetRng$, lOrder, Optional Wks As Worksheet)

If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(sKey), _
SortOn:=xlSortOnValues, Order:=lOrder, _
DataOption:=xlSortNormal
.SetRange Range(sSetRng): .Header = xlNo
.MatchCase = False: .Orientation = xlTopToBottom
.SortMethod = xlPinYin: .Apply
End With 'Wks.Sort
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion