Consolidate Values Complicated Methodology

[DaRockVBA]

I need help trying to consolidate trial balance values from specific sub accounts into general accounts. I have tried many different loops, offsets, filters and copying combinations and have failed miserably. I am using Excel 2010 and running Windows 7 Professional.

The example I want to do is as follows

Start Table - This is an example of the kind of data I would be viewing

Spec. Acct Balance

10000.0001 800.00
10000.0003 700.00
10000.0005 300.00

11000.0002 10000.00
11000.0004 5000.00

12000.0005 100000.00

End Table - I would like to consolidate the values into a formula that would display a total

Gen. Acct. Balance(Displayed Value) Formula In "Balance" Cell
10000 1800.00 =800.00+700.00+300.00 or =sum(800.00,700.00,300.00)
11000 15000.00 =10000.00+5000.00 or =sum(10000.00,5000.00)
12000 100000.00 =100000.00 or =sum(100000.00)

Please HELP!

Thank You

 

[Andrew Poulsom]

Welcome to MrExcel.

You can try a formula like:

=SUMPRODUCT(--(LEFT(A$2:A$7,5)="10000"),B$2:B$7)

If the number 10000 is in C2:

=SUMPRODUCT(--(LEFT(A$2:A$7,5)=C2&""),B$2:B$7)

 

[Weazel]

have you tried something like =SUMIF(A3:A10,10000,C3:C10) ?

Ok, my fault, I miscopied data

 

[DaRockVBA]

Yeah I could use a those formulas if all I needed was the end result to be a value but I need the cell that would be holding the consolidated value to be the specific account values actually added together.

For Example if cell D2 was the consolidated value of all of the 10000 accounts then I would need the formula in D2 to be "=800.00+700.00+300.00 or =sum(800.00,700.00,300.00)". I am trying to do this without hand keying it as the repetition of completing this task as many times as I would need to, would be rather daunting.

This is my dilemma and why I need VBA to do it. I can do a lot of Macros but this one has appeared to be outside of my knowledge base or possibly just my own creativity.

Thank you for the responses so far.

 

[DaRockVBA]

I am new to this forum, but what is the likely hood of other's responding to this post if I posted it on Friday and now there are hundreds of posts in front of mine now at this point?

 

[AngelJ]

I am new to this forum, but what is the likely hood of other's responding to this post if I posted it on Friday and now there are hundreds of posts in front of mine now at this point?

I think the solution to your problem took more time than people were willing to put into it, but I was bored so here goes. Here is a user defined function:

Option Explicit
Public Function SUMIF_ARITHMETIC(ByRef CriteriaRange, ByRef Criteria, ByRef SumRange As Range) As String
Dim CriteriaValues
Dim CriteriaValue
Dim SumValues
Dim i As Long
Dim OutputString As String
Dim Total As Double

Select Case TypeName(CriteriaRange)
    Case "Variant()"
        CriteriaValues = CriteriaRange
    Case "Range"
        CriteriaValues = CriteriaRange.Value
End Select

CriteriaValue = Criteria
SumValues = SumRange.Value

For i = LBound(CriteriaValues) To UBound(CriteriaValues)
    If CriteriaValues(i, 1) = CriteriaValue Then
        Total = Total + SumValues(i, 1)
        If SumValues(i, 1) >= 0 And OutputString <> "" Then
            OutputString = OutputString & "+"
        End If
        OutputString = OutputString & SumValues(i, 1)
    End If
Next i

SUMIF_ARITHMETIC = Total & "=" & OutputString
End Function

Let me know if you need help getting that going.

I didn't want to shoehorn a function into this particular situation, so to make it a bit more universal I allowed it to take either a range or an array. You can use it just like SUMIF (although it may not behave exactly the same) or, as in your case, you can provide an array as the criteria range ( VALUE(LEFT(A$2:A$7,5)) ).

You said you had some experience with macros so I didn't explain every detail, but please let me know if any of this didn't make sense.

 

[DaRockVBA]

This works perfect! I am really appreciative of your help!

I had never thought to just make a new function, I was trying to do it all as a big subroutine that placed the value in the cell I wanted it to go. I wish I would have thought about that before, it makes more sense. I appreciate your brilliant expertise.

This also helps because it gives me new possibilities to consider in modeling financial data. I have been honing my VBA skills since last August as I started to feel like just being familiar with built in functions was not enough and also rather slow. creating macros and applying them to my models has increased my productivity exponentially.

Thank you again for your help.

 

[AngelJ]

No problem. I'm glad that worked for you. It sounds like you'll be able to modify that function to suit you if necessary.

Good luck on honing those VBA skills, there's always more for us to learn!