+ Reply to Thread
Results 1 to 10 of 10

compare between 2 excel in folder and output the result in a main excel

  1. #1
    Registered User
    Join Date
    10-09-2018
    Location
    canada
    MS-Off Ver
    2016
    Posts
    41

    compare between 2 excel in folder and output the result in a main excel

    Hi,

    Can you please help..
    i need to compare a set of corresponding "Product excel" Vs "Consolidate excel" every month, from folder D:\test\Files.
    In "A012_ProductFile_January2020.xlsx " cells B2(CODE), B3(ID), D3(CATEGORY), E3(AMOUNT)
    against A012_Consolidate_January2020.xlsx cells A3, A4(ID), C4(CATEGORY), D4(AMOUNT)

    I have a Main "COMPARE" excel contain the compare button and details for header
    PRODUCT FILE | CONSOLIDATE FILE | RESULT |DATE COMPARISON DONE

    e.g
    A012_ProductFile_January2020.xlsx
    A012_Consolidate_January2020.xlsx
    B014_ProductFile_January2020.xlsx
    B014_Consolidate_January2020.xlsx
    C020_ProductFile_January2020.xlsx
    D022_Consolidate_January2020.xlsx


    The result can be match, mismatch, file missing.
    Attached Files Attached Files

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

    Question Re: compare between 2 excel in folder and output the result in a main excel


    Hi,

    which type of file can be 'missing', product or consolidate ?

  3. #3
    Registered User
    Join Date
    10-09-2018
    Location
    canada
    MS-Off Ver
    2016
    Posts
    41
    Quote Originally Posted by Marc L View Post

    Hi,

    which type of file can be 'missing', product or consolidate ?
    any of file either product or consolidate being compared can be missing from the folder

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

    Question Re: compare between 2 excel in folder and output the result in a main excel


    According to your explanation only each cell must be compared like for example Product cell B2 vs Consolidate cell A3
    or you misexplained the need ?

  5. #5
    Registered User
    Join Date
    10-09-2018
    Location
    canada
    MS-Off Ver
    2016
    Posts
    41

    Re: compare between 2 excel in folder and output the result in a main excel

    Yes need to compare cell B2 from ProductFile against cells A3 in Consolidate File
    Also to compare if cells B3(ID), D3(CATEGORY), E3(AMOUNT) from product File against consolidated file A4(ID), C4(CATEGORY), D4(AMOUNT).

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

    Question Re: compare between 2 excel in folder and output the result in a main excel


    So according to your explanation you just want to compare the headers row ?

  7. #7
    Registered User
    Join Date
    10-09-2018
    Location
    canada
    MS-Off Ver
    2016
    Posts
    41
    Quote Originally Posted by Marc L View Post

    So according to your explanation you just want to compare the headers row ?
    i need to compare the data between these two excel for these cells

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

    Cool Try this !


    Obviously a smart explanation should be 'data in columns' rather than 'B3 vs A4 cells', is it so difficult ?!
    As always the better initial explanation & attachment, the quicker and more targeted answer

    So according to your explanation & attachment level, a VBA beginner starter
    you should amend if you forgot anything whatever in explanation or attachment,
    the workbook must be saved as binary (.xlsb) or even as macro (.xlsm) in the same folder than the source files
    or in case of a different folder just update the path variable (edit v2) :

    PHP Code: 
    Sub Button1_Click()
      Const 
    "&""""&"
        
    Dim P$, R&, VF$, WN$, LM%, VCVP
            P 
    ThisWorkbook.Path Application.PathSeparator
            R 
    1
            V 
    = Array("Product file missing""Match""Mismatch")
            
    ActiveSheet.UsedRange.Offset(1).Clear
            Application
    .ScreenUpdating False
              F 
    Dir("*_ProductFile_*.xlsx")
        While 
    ""
            
    1
            Rows
    (R).Columns("F:H") = Array(F, , "Consolidate file missing")
              
    Dir
        Wend
              W 
    = [F1].CurrentRegion.Columns(1).Value2
              F 
    Dir("*_Consolidate_*.xlsx")
        While 
    ""
               
    Replace$(F"Consolidate""ProductFile")
               
    Application.Match(NW0)
               
    = -IsNumeric(L)
            If 
    M Then
                With Workbooks
    .Open(F).ActiveSheet
                    With 
    .UsedRange.Rows("5:" & .UsedRange.Rows.Count).Columns
                        VC 
    = .Parent.Evaluate(.Item(1).Address & .Item(3).Address & .Item(4).Address)
                    
    End With
                       
    .Parent.Close False
                End With
                With Workbooks
    .Open(N).ActiveSheet
                    With 
    .UsedRange.Rows("4:" & .[B3].End(xlDown).Row).Columns
                        VP 
    = .Parent.Evaluate(.Item(2).Address & .Item(4).Address & .Item(5).Address)
                    
    End With
                       
    .Parent.Close False
                End With
                    M 
    - (UBound(VC) <> UBound(VP))
                    If 
    1 Then M - (Application.Count(Application.Match(VCVP0)) <> UBound(VC))
            Else
                
    1:  R
            End 
    If
                
    Rows(L).Columns("G:H") = Array(FV(M))
              
    Dir
        Wend
            Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 04-09-2020 at 10:07 AM. Reason: optimization

  9. #9
    Registered User
    Join Date
    10-09-2018
    Location
    canada
    MS-Off Ver
    2016
    Posts
    41

    Re: Try this !

    ya save it as macro format & files will be in same folder.. thxs i will check it & let u knw
    how to add the date that the comparison was done in it in column I?
    Last edited by Akika; 04-08-2020 at 08:50 AM.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: compare between 2 excel in folder and output the result in a main excel


    Post #8 code edited for a better checking

+ 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. [SOLVED] Scripting Dictionary to compare two columns and output a result
    By sriley5 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-15-2018, 09:34 PM
  2. Compare attendance machine output with excel
    By Jandro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2015, 04:52 AM
  3. [SOLVED] Compare two columns and output a result
    By Alidamalang110 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2015, 01:06 PM
  4. Sum excel files in one folder to a main document outside of the folder
    By patel.1557 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2014, 08:27 AM
  5. Replies: 2
    Last Post: 11-13-2012, 02:56 PM
  6. [SOLVED] Can't specify the folder for the output of the batch file invoked by Excel VBA
    By merthum in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2012, 09:26 AM
  7. Can't specify the folder for the output of the batch file invoked by Excel VBA
    By merthum in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2012, 07:41 AM

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