Announcement

Collapse
No announcement yet.

Match Cells And Sum

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match Cells And Sum

    I have a list of account numbers is row A. If the numbers match I want to go to column D and sum the total. I have searched the forum and can't find anything formulas specific to add a column when the criteria change (in column A). Is this possible in VB.
    Thanks for any help

  • #2
    Re: Match Cells And Sum

    Sounds as if you can do this with SUMIF or SUMPRODUCT formulas (not VBA). Can you attach a sample workbook?
    Last edited by ByTheCringe2; December 19th, 2006, 03:41.
    .

    Comment


    • #3
      Re: Match Cells And Sum

      SUMIF() will probably be a bit more efficient (but unless your file is very large you probably won't be able to tell the difference), but here is a link to a SUMPRODUCT() example file that may help with syntax.

      http://members.cox.net/rtcsolutions/...20Examples.xls
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: Match Cells And Sum

        Thanks for your help.

        the problem I am having with sumif or sumproduct is there is not several constants. I understand how to do if if you are looking for one particular thing.

        For example:
        column a column D
        Type amount
        32 1000
        33 2000
        55 3000
        32 1000
        55 50
        66 3000

        For ever match in column "A" sum info in column"D"

        so all the numbers that match in column A need to sum to the total in column d. I would like to include this as a function in VB. Is this possible?

        Comment


        • #5
          Re: Match Cells And Sum

          Ok. For this approach you are best off using a pivot table. Have a look at:
          Pivot Tables
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6
            Re: Match Cells And Sum

            I have to use VB. All the other data is pulled that way. Is it possible to pick up a pivot table in VB? I am trying to learn all of this.

            I was told this has to be automated with very few key strokes.

            Comment


            • #7
              Re: Match Cells And Sum

              Yes, you can create pivot tables using VBA. Record a macro while you create the Pivot Table you want to get a good start on the code you'll need.

              Recorded code can almost always be consolidated, but will run just fine as recorded (assuming you want to exacly replicate what you recorded).
              Best Regards,
              Tom
              ---------------------------
              Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

              Comment


              • #8
                Re: Match Cells And Sum

                I would rather include a sumif or sumproduct if possible in VB. Is there a wildcard that would pick up matching criteria? I am totally lost here............
                I was just told no pivot tables.

                Comment


                • #9
                  Re: Match Cells And Sum

                  Hmmm ... without knowing your exact application, data layout, etc. it's abit hard to say, but it sure sounds like your users are asking you to provide the hard way what a pivot table is designed to do.

                  From what you say, maybe you need to use Advanced Filter to create a unique list of the entries in column A, and then write your SUMIF or SUMPRODUCT functions (and the column A target value in an adjacent column) to the appropriate number of cells in the column where you want your answers. All in VBA from what you say.

                  Sounds more like a homework assignment than a productive work assignment. Do your users know why they don't want a pivot table? That amounts to ignoring Excel's most powerful built-in tool.
                  Best Regards,
                  Tom
                  ---------------------------
                  Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                  Comment


                  • #10
                    Re: Match Cells And Sum

                    This is driving me nuts. I guess maybe I didn't explain what I was trying to accomplish. I need the aggregate of each account number and also need to carry over all the information in that row pertaining to that account number.

                    Sumif or sumprouduct ask a specific thing to look for. The account numbers vary. I have attached a file.
                    Thanks for any help

                    Comment


                    • #11
                      Re: Match Cells And Sum

                      Sorry, didn't attach the file.
                      Attached Files

                      Comment


                      • #12
                        Re: Match Cells And Sum

                        See Tom's reply here. I would say this is the best way to go with.
                        Kris

                        ExcelFox

                        Comment


                        • #13
                          Re: Match Cells And Sum

                          Thanks, but as I said I cannot use a pivot table. The data is huge and there are several macros pulling from the data. My boss says he wants another macro without a pivot table.

                          I can't sleep thinking about this!

                          Comment


                          • #14
                            Re: Match Cells And Sum

                            Hi,

                            Try,

                            Sub test()
                            Dim sWs As Worksheet, dWs As Worksheet
                            Dim dRng As Range, Concat As Range
                            Dim lRow As Long

                            Set sWs = Sheets("Info")
                            Set dWs = Sheets("Totals")
                            lRow = sWs.Range("A" & Rows.Count).End(xlUp).Row
                            Set Concat = sWs.Range("D2:D" & lRow)
                            Set dRng = dWs.[a1]

                            dRng.CurrentRegion.ClearContents
                            With Concat
                            .Formula = "=a2&""#""&b2"
                            .Value = .Value
                            End With
                            sWs.[d1] = "Concat"
                            With Concat
                            .Offset(-1).Resize(lRow, 1).AdvancedFilter action:=xlFilterCopy, _
                            copytorange:=dRng, unique:=True
                            End With
                            With dWs.Range("C2:C" & dWs.[a65536].End(xlUp).Row)
                            .Formula = "=sumif('" & sWs.Name & "'!d$2:d$" & lRow & ",a2,'" _
                            & sWs.Name & "'!c$2:c$" & lRow & ")"
                            .Value = .Value
                            .NumberFormat = "$#,###.0"
                            End With
                            With dWs.Range("A2:A" & dWs.[a65536].End(xlUp).Row)
                            .TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                            :="#", FieldInfo:=Array(Array(1, 1), Array(2, 1))
                            End With
                            dWs.[a1] = "Account#": dWs.[b1] = "Name": dWs.[c1] = "Balance"
                            sWs.Columns(4).Clear
                            End Sub


                            HTH
                            Kris

                            ExcelFox

                            Comment


                            • #15
                              Re: Match Cells And Sum

                              Thank you so very much for your help. It works.

                              I just have one question. I know it doesn't make sense, and it doesn't to me either, but there are several people inputting into the database I am pulling from and each time there is an account started for an existing customer they use the same account number and input the name. The interesting thing I found was that this code aggragates by account number but a few customers might be entered as Thomas A. Smith and another as Thomas A. Smith,the same account number, but just an extra space in the name. The code pulls all the account numbers matching Thomas A. Smith and totals and also gives another total for Thomas A. Smith (who has the same account number). I realize it is an input error on the companys part. Junk in junk out, right? I don't see any way around this. Was just curious about your opinion. Maybe I can get some of the junk cleaned up!

                              Thanks!
                              Tiffany

                              Comment

                              Working...
                              X