Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
Also I need to compare the dinal data with the data on another sheet
(the same accounts but different totals) to get difference. Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
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''. "Loop" wrote: Also I need to compare the dinal data with the data on another sheet (the same accounts but different totals) to get difference. Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
On Aug 12, 12:56*pm, ryguy7272
wrote: 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''.. "Loop" wrote: Also I need to compare the dinal data with the data on another sheet (the same accounts but different totals) to get difference. Thanks,- Hide quoted text - - Show quoted text - I can do it using a Pivot table but I'd like to get a vba code. Thanks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
On 12 авг, 14:19, arjen van... wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count totals for the same accounts
On 12 авг, 14:19, arjen van... wrote:
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 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count subset-totals in column | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Using AND + COUNTIF to filter / count totals | Excel Discussion (Misc queries) | |||
pivot table question about count and totals | Excel Discussion (Misc queries) | |||
count of totals for pivot table | Excel Discussion (Misc queries) |