+ Reply to Thread
Results 1 to 9 of 9

Compare Data with Sumif Formula

  1. #1
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Compare Data with Sumif Formula

    Hi

    see the example file

    Seeking help to create VBA code

    Sheet1 and Sheet 2 I have the data to be compared

    In Example data Sheet1 data starts from B7 and N7

    Similarly in Sheet2 data starts from S4 and AK4

    I need to compare this two data and list the differences in Sheet "Results"

    Please help to create VBA code
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Compare Data with Sumif Formula


    Hi,

    according to the expected result do you really need the formulas or just the values ?

  3. #3
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Compare Data with Sumif Formula

    Hello Marc L

    Need results with formulas. Thanks

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    According to your attachment a VBA demonstration for starters :

    PHP Code: 
    Sub Demo1()
      Const 
    "<>0"
        
    Dim Rg(1) As RangeR&, S%, WV(1 To 3)
        
    Set Rg(0) = [Results!B4]
            
    Rg(0).CurrentRegion.Offset(1).Clear
            R 
    2
        
    For 1 To 2
            With Sheets
    (S)
                    
    Set Rg(1) = .UsedRange.Find("Data", , xlValues11):  If Rg(1Is Nothing Then Erase RgBeep: Exit Sub
                    W 
    Application.Match("Amount", .Rows(Rg(1).Row), 0):   If IsError(WThen Erase RgBeep: Exit Sub
                With 
    .Range(Rg(1)(2), Rg(1).End(xlDown))
                    
    V(S) = "=SUMIF(" & .Address(, , , True) & ",B5," & .Columns(Rg(1).Column).Address(, , , True) & ")"
                   
    .Copy Rg(0)(R)
                    
    + .Count
                End With
            End With
        Next
            Rg
    (0).CurrentRegion.Columns(1).RemoveDuplicates 11
            V
    (3) = "=C5-D5"
        
    With Rg(0).CurrentRegion.Rows
                
    .Item("2:" & .Count).Columns("B:D").Formula V
            
    If Application.CountIf(.Columns(4), C) - 1 Then
                
    .Columns(4).AutoFilter 1C
                
    .Item("2:" & .Count).Interior.Color vbYellow
                
    .AutoFilter
            End 
    If
            
    With .Item(.Count 1).Columns("B:D")
                 .
    Borders(8).Weight 2
                 
    .Formula "=SUM(C5:C" & .Row ")"
            
    End With
        End With
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  5. #5
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Compare Data with Sumif Formula

    Hello Marc L
    Thank you so much for your help. The code is working fine on test data shared. As mentioned in sample work book I was expected, to select the colum range manually. Instead of finding "data" and "amount". And sheet names also not fixed one.

    But your method also good. But I need to use this code to multiple workbooks., so can you please help to change the code little bit as per my revised sample work book.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this …


    My VBA demonstration revamped :

    PHP Code: 
    Sub Demo1r()
      Const 
    "<>0"
        
    Dim Rg(2) As RangeS%, R&, V(1 To 3)
        
    Set Rg(0) = [Results!B4]
            
    Rg(0).Parent.UsedRange.Clear
        
    For 1 To 2
            Sheets
    (S).Activate
            On Error Resume Next
        
    Do
            
    Set Rg(1) = Application.InputBox(" Select names column :""Sheet #" SType:=8)
            If 
    Err.Number Then Erase Rg: Exit Sub
        Loop Until Rg
    (1).Areas.Count And Rg(1).Columns.Count And Not IsEmpty(Rg(1)(2))
            
    On Error GoTo 0
            
    If Rg(1).Count 1 Then Set Rg(1) = Range(Rg(1), Rg(1).End(xlDown))
        If 
    1 Then
            Rg
    (1).Copy Rg(0)
            
    Rg(1).Count 1
        
    Else
            
    Rg(1).Rows("2:" Rg(1).Rows.Count).Copy Rg(0)(R)
            If 
    Rg(1)(1).Text <> Rg(0).Text Then Rg(0).Value2 Rg(0).Text "/" Rg(1)(1).Text
        End 
    If
            
    Rg(0)(11).Value2 Rg(1).Parent.Name
            Rg
    (1).Parent.Activate
            On Error Resume Next
        
    Do
            
    Set Rg(2) = Application.InputBox(" Select values column :""Sheet #" SType:=8)
            If 
    Err.Number Then Erase Rg: Exit Sub
            
    If Rg(2).Count And Not IsEmpty(Rg(2)(2)) Then Set Rg(2) = Range(Rg(2), Rg(2).End(xlDown))
        
    Loop Until Rg(2).Areas.Count And Rg(2).Columns.Count And _
                   Rg
    (2).Parent.Name Rg(1).Parent.Name And Rg(2).Rows.Count Rg(1).Rows.Count
            On Error 
    GoTo 0
            V
    (S) = "=SUMIF(" Rg(1).Address(, , , True) & ",B5," Rg(2).Address(, , , True) & ")"
        
    Next
            Rg
    (0).CurrentRegion.Columns(1).RemoveDuplicates 11
            Rg
    (0)(14).Value2 "GAP"
            
    V(3) = "=C5-D5"
        
    With Rg(0).CurrentRegion.Rows
            
    .Item("2:" & .Count).Columns("B:D").Formula V
        
    If Application.CountIf(.Columns(4), C) - 1 Then
            
    .Columns(4).AutoFilter 1C
            
    .Item("2:" & .Count).Interior.Color vbYellow
            
    .AutoFilter
        End 
    If
        
    With .Item(.Count 1).Columns("B:D")
             .
    Borders(8).Weight 2
             
    .Formula "=SUM(C5:C" & .Row ")"
        
    End With
            
    .Parent.Activate
        End With
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 03-18-2022 at 10:13 PM. Reason: optimization …

  7. #7
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Compare Data with Sumif Formula

    HELLO MARC L
    thanks for your help.

    may be i am selecting wrong range when its prompt. could you please tell when its ask to select "sheet names column" which column to select

    i am getting run time error 1004 on the below line

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Compare Data with Sumif Formula


    So weird as it works like a charm on my side with your attachment …

    You must select either the first cell of the column like 'Data' or
    'Data' and some below cells if you not need all cells under 'Data' …

  9. #9
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    141

    Re: Compare Data with Sumif Formula

    Hello MARC L

    Thank you so much for your help. Yes its works like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula / Table pulling weekly data to compare daily data
    By MatrixFX in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-16-2020, 02:04 PM
  2. SUMIF Matrix formula -> get data from external (closed) data sheet
    By matzina in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2016, 08:07 AM
  3. Using SUMIF to compare two cells for equality and then sum if there is a match
    By methuselah90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2014, 05:56 AM
  4. Replies: 4
    Last Post: 08-12-2014, 09:23 AM
  5. I need a for formula to compare data in same row and Put 1
    By Infoway123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2013, 12:44 PM
  6. how to compare data in one formula
    By legolas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2011, 05:02 AM
  7. Look up and compare data in the same formula.
    By gcastilleja in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 05-06-2009, 09:10 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1