Count totals for the same accounts

L

Loop

Hi all,
I'm new in VBA.
Could anyone helps me with vba code in excel to count totals? I have
two columns:

Account Amount
19216801 5
19216803 10
19216808 2
19216801 3
19216801 5
19216803 6
19216801 34
19216801 21
19216808 45

I'd like to see them like this:
Account Amount
19216801 68
19216803 16
19216808 47

Thanks
 
L

Loop

Also I need to compare the dinal data with the data on another sheet
(the same accounts but different totals) to get difference.

Thanks,
 
R

ryguy7272

Have you tried a Pivot Table or Subtotals?
Data > Text to Column > Space > Finish
Insert a row on the top. Data > Pivot Table > Finish
If you use a subtotal, sort the data > Data > Subtotal > Sum > Add subtotal
to...Ok

HTH,
Ryan---
 
L

Loop

Have you tried a Pivot Table or Subtotals?
Data > Text to Column > Space > Finish
Insert a row on the top.  Data > Pivot Table > Finish
If you use a subtotal, sort the data > Data > Subtotal > Sum > Add subtotal
to...Ok

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..






- Show quoted text -
Click to expand...

I can do it using a Pivot table but I'd like to get a vba code.

Thanks,
 
A

arjen van...

The totals for each can be calculated using an array for all the data and
then looking for each account #:

Sub CountArray()

Dim arrCount As Variant
With Sheets("Sheet1")
arrCount = .Range(.Range("A2"), .Range("A2").End(xlDown) _
.End(xlToRight)).Value
End With

Dim ac19216801, ac19216803, ac19216808 As Long
Dim i As Long

For i = LBound(arrCount, 1) To UBound(arrCount, 1)
If arrCount(i, 1) = 19216801 Then
ac19216801 = ac19216801 + arrCount(i, 2)
ElseIf arrCount(i, 1) = 19216803 Then
ac19216803 = ac19216803 + arrCount(i, 2)
ElseIf arrCount(i, 1) = 19216808 Then
ac19216808 = ac19216808 + arrCount(i, 2)
End If

Next

With Sheets("Sheet1")
.Range("B16").Value = ac19216801
.Range("B17").Value = ac19216803
.Range("B18").Value = ac19216808
End With

End Sub
 
T

Tatiana Lysenka

The totals for each can be calculated using an array for all the data and
then looking for each account #:

Sub CountArray()

    Dim arrCount As Variant
        With Sheets("Sheet1")
            arrCount = .Range(.Range("A2"), .Range("A2").End(xlDown) _
                             .End(xlToRight)).Value
        End With

    Dim ac19216801, ac19216803, ac19216808 As Long
    Dim i As Long

    For i = LBound(arrCount, 1) To UBound(arrCount, 1)
        If arrCount(i, 1) = 19216801 Then
            ac19216801 = ac19216801 + arrCount(i, 2)
        ElseIf arrCount(i, 1) = 19216803 Then
            ac19216803 = ac19216803 + arrCount(i, 2)
        ElseIf arrCount(i, 1) = 19216808 Then
            ac19216808 = ac19216808 + arrCount(i, 2)
        End If

    Next

    With Sheets("Sheet1")
        .Range("B16").Value = ac19216801
        .Range("B17").Value = ac19216803
        .Range("B18").Value = ac19216808
    End With

End Sub
Click to expand...
 
L

Loop

The totals for each can be calculated using an array for all the data and
then looking for each account #:

Sub CountArray()

    Dim arrCount As Variant
        With Sheets("Sheet1")
            arrCount = .Range(.Range("A2"), .Range("A2").End(xlDown) _
                             .End(xlToRight)).Value
        End With

    Dim ac19216801, ac19216803, ac19216808 As Long
    Dim i As Long

    For i = LBound(arrCount, 1) To UBound(arrCount, 1)
        If arrCount(i, 1) = 19216801 Then
            ac19216801 = ac19216801 + arrCount(i, 2)
        ElseIf arrCount(i, 1) = 19216803 Then
            ac19216803 = ac19216803 + arrCount(i, 2)
        ElseIf arrCount(i, 1) = 19216808 Then
            ac19216808 = ac19216808 + arrCount(i, 2)
        End If

    Next

    With Sheets("Sheet1")
        .Range("B16").Value = ac19216801
        .Range("B17").Value = ac19216803
        .Range("B18").Value = ac19216808
    End With

End Sub
Click to expand...

Thanks a lot for answering. No offence but it's not gonna work. I have
thousands rows. To write a code like this I'd spend hours.
Is it possible to to do a loop compare the first account in a row with
next one until the end. Then second one starts. But if it already was
counted, skip it.

Thanks,
 
A

arjen van...

It can be done using SQL and embedding the SQL query in a VBA procedure
(using ADO). It requires adding a reference to the ADO library in your
project. Tools > References > Microsoft ActiveX Data Objects Library.

This was done in Excel 2003, using the Acess 2003 database engine (JET). I
hope this formats okay.

Option Explicit

Sub QueryExcel()

'create the connection string
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=K:\Excel\jun09\Aug13.xls;" & _
"Extended Properties=Excel 8.0;"

'create the sql query
Dim MyQuery As String

MyQuery = "SELECT Account, SUM(Amount) " & _
"FROM [DataSheet$] " & _
"GROUP BY Account " & _
"ORDER BY Account"

'create the recordset
Dim MyRS As ADODB.Recordset
Set MyRS = New ADODB.Recordset

'open the recordset
MyRS.Open MyQuery, ConnectionString, adOpenStatic, adLockReadOnly,
adCmdText

ThisWorkbook.Sheets("Summary").Activate
ActiveSheet.Range("A1").CopyFromRecordset MyRS

MyRS.Close
Set MyRS = Nothing

End Sub

Note: You'll have to change the path in the connection string. Also the way
it's setup requires the original data & the summary you're creating to be in
different workbooks.
 
L

Loop

It can be done using SQL and embedding the SQL query in a VBA procedure
(using ADO). It requires adding a reference to the ADO library in your
project. Tools > References > Microsoft ActiveX Data Objects Library.

This was done in Excel 2003, using the Acess 2003 database engine (JET). I
hope this formats okay.

Option Explicit

Sub QueryExcel()

    'create the connection string
    Dim ConnectionString As String

    ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                                "Data Source=K:\Excel\jun09\Aug13.xls;" & _
                                "ExtendedProperties=Excel 8.0;"

    'create the sql query
    Dim MyQuery As String

    MyQuery = "SELECT Account, SUM(Amount) " & _
                     "FROM [DataSheet$] " & _
                     "GROUP BY Account " & _
                     "ORDER BY Account"

    'create the recordset
    Dim MyRS As ADODB.Recordset
        Set MyRS = New ADODB.Recordset

    'open the recordset
    MyRS.Open MyQuery, ConnectionString, adOpenStatic, adLockReadOnly,
adCmdText

    ThisWorkbook.Sheets("Summary").Activate
    ActiveSheet.Range("A1").CopyFromRecordset MyRS

    MyRS.Close
    Set MyRS = Nothing

End Sub

Note: You'll have to change the path in the connection string. Also the way
it's setup requires the original data & the summary you're creating to bein
different workbooks.
Click to expand...

Thanks a lot. I'll try to make it work.
 
Top