Solved

# excel macro to sum values

Posted on 2012-08-26
304 Views
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
``````

And I need the sum of values in sheet2 like this

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

I need a macro to do this summation based on ITEMNO
sample.xls
0
Question by:karunamoorthy

LVL 39

Expert Comment

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

LVL 7

Author Comment

it would be better to have a macro for my question pl
0

LVL 18

Accepted Solution

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
``````

Kris
0

LVL 31

Expert Comment

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

LVL 7

Author Comment

>>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

LVL 18

Expert Comment

0

LVL 7

Author Closing Comment

Thank you very much
0

## Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.