Sum values in one column based on unique values in another

Hi,

I'll get right into it:

I have the following data which is a small sample from a large pool that constantly changes:

Column A Column B
1 156234
2 178292
2 34023
3 10934
4 156705

I need to get it to look like this:

Column D Column E
1 156234
2 212315
3 10934
4 156705

I need to use VBA arrays to attain the unique values from column A and sum Column B accordingly. I have used PivotTables previously, but given the data constantly changes i thought VBA Arrays would be best suited to make it efficient as excel formulas take sometime to calculate with 30,000+ records.


Thanks for you help.

Regards
Deepak

thanks vipul, but that is taking a little while to calculate:

I tried the following with some help from google:

Sub Macro()

Dim ItemList As Long: ItemList = Range("A" & Rows.count).End(xlUp).Row
Dim UniqueItemList As Long
Dim found As Boolean: found = False

Range("C1").Value = Range("A1").Value

For i = 1 To ItemList
UniqueItemList = Range("C" & Rows.count).End(xlUp).Row
For j = 1 To UniqueItemList And Not found
If Range("A" & i).Value = Range("C" & j).Value Then
Range("D" & j).Value = Range("D" & j).Value + 1
Range("E" & j).Value = Range("E" & j).Value + Range("B" & i).Value
found = True
Exit For
End If
Next j
If Not found Then
Range("C" & UniqueItemList + 1).Value = Range("A" & i).Value
Range("D" & UniqueItemList + 1).Value = 1
Range("E" & UniqueItemList + 1).Value = Range("B" & i).Value
End If
found = False
Next i

End Sub

ItemList is ColumnA and UniqueItemList is the Output of unique values and then there is count as well as a sum of ColumnB - however it takes a while to calculate.

I need to put this into an array within VBA to make it more efficient.

Do you really need VBa for this?

In C2

=IF(ISERROR(LOOKUP(99^99,CHOOSE({1;2},"",INDEX($A$1:$A$300,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$300,$C$1:$C1,0)),0),0))))),"",LOOKUP(99^99,CHOOSE({1;2},"",INDEX($A$1:$A$300,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$300,$C$1:$C1,0)),0),0)))))

Drag/ Fill Down until a blank is returned

In D2

=IF(C2>0,COUNTIF(A:A,C2),"")

In E2

=IF(C2>0,SUMIF(A:A,C2,B:B),"")

Drag/ Fill both Down to suit the results in Column C.

N.B.
Your profile indicates you are using 2003, but your sample is clearly 2007 or above.
For 2007 and above use this in C2

=IFERROR(LOOKUP(99^99,CHOOSE({1;2},"",INDEX($A$1:$A$300,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$300,$C$1:$C1,0)),0),0)))),"")

[EDIT]
For Files with 30000 plus rows adjust the lookup range size e.g.

=IFERROR(LOOKUP(99^99,CHOOSE({1;2},"",INDEX($A$1:$A$40000,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$40000,$C$1:$C1,0)),0),0)))),"")

I can't see VBa being any faster, I might be wrong.

in D1:

PHP Code: 

=SUMPRODUCT(($A$1:$A$270=ROW())*($B$1:$B$270)) 


in C1

PHP Code: 

=ROW() 


But if you prefer to work with arrays: (clear column E first)

Sub snb()
  sn = Cells(1).CurrentRegion.Resize(, 5)

  For j = 1 To UBound(sn)
    sn(sn(j, 1), 5) = sn(sn(j, 1), 5) + sn(j, 2)
  Next

  Cells(1).CurrentRegion.Resize(, 1).Offset(, 8) = Application.Index(sn, , 5)
End Sub