Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: Match Cells And Sum

  1. #11
    Join Date
    8th February 2005
    Posts
    72

    Re: Match Cells And Sum

    Sorry, didn't attach the file.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #12
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Match Cells And Sum

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

  3. #13
    Join Date
    8th February 2005
    Posts
    72

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #14
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Match Cells And Sum

    Hi,

    Try,

    VB:
    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

  5. #15
    Join Date
    8th February 2005
    Posts
    72

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #16
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Match Cells And Sum

    Hi,

    Replace
    VB:
    .Formula = "=a2&""#""&b2" 
    
    
    with

    VB:
    .Formula = "=TRIM(A2&""#""&B2)" 
    
    
    HTH

  7. #17
    Join Date
    8th February 2005
    Posts
    72

    Re: Match Cells And Sum

    I'll try it.

    Thanks for all your help.

    Tiffany

    Excel Video Tutorials / Excel Dashboards Reports


  8. #18
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Match Cells And Sum

    Quote Originally Posted by Tiffany
    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?
    VB:
    Sub test() 
        Dim a, i As Long, b(), n As Long 
        a = Range("a1").CurrentRegion.Resize(,4).Value 
        Redim b(1 To UBound(a,1), 1 To 2) 
        With CreateObject("Scripting.Dictionary") 
            For i = 2 To UBound(a,1) 
                If Not .exists(a(i,1)) Then 
                    n = n + 1 : b(n,1) = a(i,1) 
                    .add a(i,1), n 
                End If 
                b(.item(a(i,1)),2) = b(.item(a(i,1)),2) + a(i,4) 
            Next 
        End With 
        Range("f1").Resize(n,2).Value = b 
    End Sub 
    
    
    Last edited by jindon; February 23rd, 2007 at 12:30.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #19
    Join Date
    8th February 2005
    Posts
    72

    Re: Match Cells And Sum

    Thanks, I will try it tomorrow at work!

    Tiffany

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Highlight Cells If Match Another Sheet Cells
    By grosbarbu in forum Excel General
    Replies: 12
    Last Post: October 30th, 2012, 22:28
  2. Match Cells & Shift Rows Down If No Match
    By dtwk in forum Excel General
    Replies: 10
    Last Post: March 17th, 2011, 01:50
  3. Replies: 2
    Last Post: May 20th, 2008, 14:13
  4. match 3 cells, average cell based on match and copy
    By diverdls in forum Excel General
    Replies: 7
    Last Post: September 10th, 2005, 13:38
  5. [Solved] Formulas: adding cells if other cells match...
    By Exceladdict in forum Excel General
    Replies: 5
    Last Post: November 14th, 2003, 12:44

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno