Prev: simple macro to compare lists
Next: Data moving 3
From: bud i on 7 Apr 2010 02:55
Following is my data:
A B C
1 Oranges 6
2 Oranges 5
3 Oranges 7
4 Pomegranates 19
5 Pomegranates 16

C3 should be 18, and C5 should be 35.
Next week there will more more or fewer categories with a variable number of
entities in each.

A macro to accomplish this will save me a lot of time (and errors ??).
--
“Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)
From: ozgrid.com on 7 Apr 2010 03:42
Subtotal feature or pivottable based of dynamic named range;
http://www.ozgrid.com/Excel/subtotal.htm
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm


--
Regards
Dave Hawley
www.ozgrid.com
"bud i" <ibud.no-spam(a)bellsouth.net> wrote in message
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40(a)microsoft.com...
> Following is my data:
> A B C
> 1 Oranges 6
> 2 Oranges 5
> 3 Oranges 7
> 4 Pomegranates 19
> 5 Pomegranates 16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number
> of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

From: Rick Rothstein on 7 Apr 2010 04:07
Give this macro a try...

Sub SubTotals()
Dim X As Long, LastRow As Long, LastSubTotal As Long, Fruit As String
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Fruit = Cells(StartRow, "A").Value
LastSubTotal = StartRow
For X = StartRow + 1 To LastRow + 1
If Cells(X, "A").Value <> Fruit Then
Cells(X - 1, "C").Value = WorksheetFunction.Sum(Range(Cells( _
LastSubTotal, "B"), Cells(X - 1, "B")))
Fruit = Cells(X, "A").Value
LastSubTotal = X
End If
Next
End Sub

--
Rick (MVP - Excel)



"bud i" <ibud.no-spam(a)bellsouth.net> wrote in message
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40(a)microsoft.com...
> Following is my data:
> A B C
> 1 Oranges 6
> 2 Oranges 5
> 3 Oranges 7
> 4 Pomegranates 19
> 5 Pomegranates 16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number
> of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

From: OssieMac on 7 Apr 2010 04:36
Hi Bud,

Is it really necessary to have the formula in column C adjacent to the last
record of the particular type? If not, you could set up a little table either
on the same worksheet on on another worksheet like the following with a list
of unique values of your column A.

Col E Col F
Item Total
Oranges 18
Pomegranates 35

You can then use SUMIF. See Help for more info on this.

Your formula in F2 in the above would be
=SUMIF(A:A,E2,B:B)

You only need to copy the formula down and you can add or delete items as
required.

If you decide to have your table in another worksheet then the formula would
be like the following. (Assuming the table is in columns E and F)

=SUMIF(Sheet1!A:A,E2,Sheet1!B:B)

--
Regards,

OssieMac


"bud i" wrote:

> Following is my data:
> A B C
> 1 Oranges 6
> 2 Oranges 5
> 3 Oranges 7
> 4 Pomegranates 19
> 5 Pomegranates 16
>
> C3 should be 18, and C5 should be 35.
> Next week there will more more or fewer categories with a variable number of
> entities in each.
>
> A macro to accomplish this will save me a lot of time (and errors ??).
> --
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)
 | 
Pages: 1
Prev: simple macro to compare lists
Next: Data moving 3