[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

excel macro to sum values

I have data in excel sheet1 like this


ITEMNO	DESCRIPTION	PERSON	QTY	RATE	VALUE
123	descr1  	per1	10	10	100
123	descr1  	per2	20	11	220
123	descr1  	per3	30	12	360
234	descr2  	per1	10	9	90
234	descr2  	per2	10	8	80
345	descr3  	per3	20	7	140
345	descr3  	per4	10	6	60

Open in new window



And I need the sum of values in sheet2 like this

ITEMNO	DESCRIPTION	TOT VAL	
123	descr1  	680	
234	descr2  	170	
345	descr3  	200

Open in new window

           
I need a macro to do this summation based on ITEMNO
sample.xls
0
karunamoorthy
Asked:
karunamoorthy
1 Solution
 
nutschCommented:
Why do you think you need a macro. Wouldn't a pivot table provide you the information you need? See attached file for pivot sample.
sample.xls
0
 
karunamoorthyAuthor Commented:
it would be better to have a macro for my question pl
0
 
krishnakrkcCommented:
Hi,

Try this.

Sub kTest()
    
    Dim ka, i As Long, x
    
    ka = Worksheets("Sheet1").Range("a1").CurrentRegion.Resize(, 6).Value2
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 2 To UBound(ka, 1)
            x = .Item(ka(i, 1))
            If IsArray(x) Then
                .Item(ka(i, 1)) = Array(ka(i, 2), x(1) + ka(i, 6))
            Else
                .Item(ka(i, 1)) = Array(ka(i, 2), ka(i, 6))
            End If
        Next
        If .Count Then
            Worksheets("Sheet2").Range("a1:c1") = Application.Index(ka, 1, Array(1, 2, 6))
            Worksheets("Sheet2").Range("a2").Resize(.Count) = Application.Transpose(.keys)
            Worksheets("Sheet2").Range("b2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
        End If
    End With

End Sub

Open in new window


Kris
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Or you can use the SUMIF function, in sheet2 column C

=SUMIF(Sheet1!$A:$A,$A1,Sheet1!$F:$F)

Where A1 contains the ITEMNO and column B contains the Item Description, although not required for above.

Copy this down to required rows and A1 will adjust to new row.

Thanks
Rob H
0
 
karunamoorthyAuthor Commented:
>>krishnakrkc,
The macro given by you works great! Could you pl give explanation or comment how it works, I have very little knowledge regarding excel macro.
0
 
karunamoorthyAuthor Commented:
Thank you very much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now