Help with Excel VBA - Summarize Data

[esib]

Hello

I have worked with Word and Outlook, but am new to Excel VBA and am wondering if someone can get me started with this. I have a query tool that loads in Excel, from SAP. The data may look like this:

RC Proj Amount
ABCDEF AA $25.00
ABCDER AA $25.00
ABCDYT AA $25.00
ABDFGE BB $25.00
ABDGTY BB $25.00
ABDCSA CC $25.00
HGFTYR DD $25.00
HGFBHY DD $25.00

I would like to write a macro that cuts RC off to 3 characters (actually any amount, but will handle that once I get this down for just one case) and then summarizes the data..So the result would be:

RC Proj Amount
ABC AA $75.00
ABD BB $50.00
ABD CC $25.00
HGF DD $50.00

I need to be able to do this under the following condidtion: The only thing that is certain is that RC is the first field. By that I mean the numeric fields to be summarized could be anywhere, there could be 3 to 50 columns, and there could be 10 to 60,000 rows.

Could someone get me started?

Thanks

 

[foundryqa]

esib,

This may not be the most efficient way, but I would do something like this

[Algorithm]

Add a column right before the RC column.
Add a formula to each cell in the pre-RC column that uses the "left(RC, 3)" function to get the first 3 letters of the value in each RC field. Continue this until you come to a blank space in RC.
Now sort the entire dataset by the new column.
Now set up a variable in vba to keep track of which RC value you are on. ie "ABC"
In another variable keep a running total of Amount.
As soon as RC changes, output the running total to a cell in another sheet along with its associated RC value.
Set the running total to 0, set the RC variable to the new RC value and continue on.

[/Algorithm]

If this isn't enough to get you started, please let me know.

Foundry.

 

[PHV]

You may also consider SUBTOTAL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[esib]

Thanks for the advice. Here is something I threw together for 2 columns, RC and Amount. Could you let me know if this is a good method (I am extremely new to Excel VBA)? Also, if I were working with say 15 columns, would building an array based on the number of columns to hold the data, instead of individual variables, be the best idea?

Public Sub try()
Dim Cell As Range
Dim myRange As Range
Dim hold_RC As String
Dim Current_RC As String
Dim Tot_Amt As Currency
Dim myRange2 As Range
Dim Cell2 As Range
' copy header row over to 2nd sheet
Sheets(1).Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

'Go back to first sheet and sort according to RC
Sheets(1).Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'set cell range for loop
Set myRange = Range("A2:A60000")
Set myRange2 = Sheets(2).Range("A2:A60000")

'initialize hold RC and Tot_AMT
hold_RC = Left(Cells(2, 1), 3)
Tot_Amt = 0

For Each Cell In myRange
Sheets(1).Select
Cell.Select
If Cell = "" Then
Exit For
End If
Current_RC = Left(Cell, 3)

If Current_RC = hold_RC Then
Tot_Amt = Tot_Amt + Cell.Offset(0, 1)
Else
Sheets(2).Select
For Each Cell2 In myRange2
Cell2.Select
If Cell2 = "" Then
Cell2 = hold_RC
Cell2.Offset(0, 1) = Tot_Amt
Exit For
End If
Next
Tot_Amt = Cell.Offset(0, 1)
hold_RC = Current_RC
End If
Next
Sheets(2).Select
For Each Cell2 In myRange2
Cell2.Select
If Cell2 = "" Then
Cell2 = hold_RC
Cell2.Offset(0, 1) = Tot_Amt
Exit For
End If
Next

End Sub

 

[foundryqa]

esib,

I don't have a lot of time to today, unfortunately. One thought that I had was if you take your idea for an array and couple it with a variable that you pass into the procedure that contains the amount of columns you want to sum, you may beable to redim the array to the proper size. I would have to tinker with it though.

Also, I would change the myrange and myrange2 to have an upper limit associated with the "dirty" area of the spreadsheet. This is the area that is actually being used. There are differing opinions on which is the best method to determine this, so I would look at a few sources. One easy method is to use the range.end(xldown) address. Refer to the help in vba excel for details.

Foundry