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
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).
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 D = "&""¤""&" Dim P$, R&, V, F$, W, N$, L, M%, VC, VP P = ThisWorkbook.Path & Application.PathSeparator R = 1 V = Array("Product file missing", "Match", "Mismatch") ActiveSheet.UsedRange.Offset(1).Clear Application.ScreenUpdating = False F = Dir(P & "*_ProductFile_*.xlsx") While F > "" R = R + 1 Rows(R).Columns("F:H") = Array(F, , "Consolidate file missing") F = Dir Wend W = [F1].CurrentRegion.Columns(1).Value2 F = Dir(P & "*_Consolidate_*.xlsx") While F > "" N = Replace$(F, "Consolidate", "ProductFile") L = Application.Match(N, W, 0) M = -IsNumeric(L) If M Then With Workbooks.Open(P & F).ActiveSheet With .UsedRange.Rows("5:" & .UsedRange.Rows.Count).Columns VC = .Parent.Evaluate(.Item(1).Address & D & .Item(3).Address & D & .Item(4).Address) End With .Parent.Close False End With With Workbooks.Open(P & N).ActiveSheet With .UsedRange.Rows("4:" & .[B3].End(xlDown).Row).Columns VP = .Parent.Evaluate(.Item(2).Address & D & .Item(4).Address & D & .Item(5).Address) End With .Parent.Close False End With M = M - (UBound(VC) <> UBound(VP)) If M = 1 Then M = M - (Application.Count(Application.Match(VC, VP, 0)) <> UBound(VC)) Else R = R + 1: L = R End If Rows(L).Columns("G:H") = Array(F, V(M)) F = 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 …
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?
Bookmarks