summing on a unique list
 

I have a spreadsheet that I used the following function to create a unique
list.

Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

' Count the number of used rows
Set A = Worksheets(1)
Set R = A.UsedRange.Cells
stRng = R.Rows.Count

Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("G" & stRng + 4), Unique:=True

For each unique value I can have several rows that have amounts in them.
What I need to do is create a sum function based on the unique list. Can
this be down, and how?

TIA

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

summing on a unique list
 

Add this

Range("G" & stRng + 4).End(xlDown).Offset(1, 0).FormulaR1C1 =
"=SUM(R25C:R[-1]C)"


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Cyberwolf" wrote in message
...
I have a spreadsheet that I used the following function to create a unique
list.

Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending,
Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

' Count the number of used rows
Set A = Worksheets(1)
Set R = A.UsedRange.Cells
stRng = R.Rows.Count

Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("G" & stRng + 4), Unique:=True

For each unique value I can have several rows that have amounts in them.
What I need to do is create a sum function based on the unique list. Can
this be down, and how?

TIA

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

summing on a unique list
 

Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("G" & stRng + 4), Unique:=True

Dim rng1 as Range
set rng1 = Range("G" & stRng + 4)
set rng1 = Range(rng1,rng1.End(xldown))
rng1.offset(0,1).Formula = "=Sumif($I$2:$I$" & stRng & "," & _
rng1.Address(0,0) & ",$J$2:$J$" & stRng & ")"

This assumes the column you are summing is column J. Adjust to reflect the
column you are summing.

--
Regards,
Tom Ogilvy


"Cyberwolf" wrote:

I have a spreadsheet that I used the following function to create a unique
list.

Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

' Count the number of used rows
Set A = Worksheets(1)
Set R = A.UsedRange.Cells
stRng = R.Rows.Count

Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("G" & stRng + 4), Unique:=True

For each unique value I can have several rows that have amounts in them.
What I need to do is create a sum function based on the unique list. Can
this be down, and how?

TIA

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf