Compare Data from multiple workbooks inside a folder- show the O/P in a Template- P/F

[akiran]

Hi,

Can some one help me with the below requirement?

Data in multiple workbooks inside a single folder to be compared(data in each and every cell) and result to be populated in a workbook Template with pass or fail status if any mismatch in data in any of the cells.
No of workbooks varies depending on the requirement.
Work books to be compared will be of same name with slight change and data range inside both the workbooks will be same.

In detail : I have workbooks inside a folder named "automation"
abc_exp_1.xls
abc_act_1.xls
abc_exp_2.xls
abc_act_2.xls
abc_exp_3.xls
abc_act_3.xls
abc_exp_4.xls
abc_act_4.xls
abcd_efgh_exp_1.xls
abcd_efgh_act_1.xls
abcd_efgh_exp_2.xls
abcd_efgh_act_2.xls
abcd_efgh_exp_3.xls
abcd_efgh_act_3.xls
abcd_efgh_exp_4.xls
abcd_efgh_act_4.xls

I have a master workbook inside the same folder "automation" named - Testresult.xls along with above mentioned xls files

Task:Compare data from similar named xls files and show o/p as pass/fail on Template as per the scenario(suffix) 1 , 2 , 3 , 4 ...

Note: I will have expected results for scenarios (1/2/3...)for category (abc / abcd_efgh / ....) in one workbook and actual result in another workbook, thats were workbooks are differentiated with "exp / act" in the name suffixed with scenario no and prefixed category name.

No of categories and scenarios will be dependent on requirement.

Eg in detail : Data to be compared between two work books are : abc_exp_1.xls and abc_act_1.xls
like wise data to be compared between next set of two work books are : abc_exp_2.xls and abc_act_2.xls
in similar front data to be compared between all their respective pair(exp/act) of work books and final output to be entered on master template sheet as pass/fail if any mismatch found.

Any help would be greatly appreciated !!!

Thanks,
Kiran

[vbfbryce]

Where do we begin? Have you started any coding, or are we starting from the very beginning here?

[akiran]

Hi,

Actually this is the final part of my project which is yet to be started. All the excel files with data to be compared were generated inside a folder were output from the previous part of this project. Since this part which I am looking for is independent to previous step, so I have mentioned this as a separate project and looking for your help.
Please confirm if any more info needed to help me out with this.

Thanks,
Kiran

[vbfbryce]

Here is some code to get you started. It will search a folder of your choice for an "Exp" file and then search for its corresponding "Act" file. Nowhere near finished, but step through and see where it gets you:

Sub compareFiles()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim myFiles() As String
    Dim i As Integer
    Dim j As Integer
    Dim currFile As String
    Dim foundString As Integer
    Dim wbExp As Workbook
    Dim wbAct As Workbook
    Dim myPath As String

    ReDim myFiles(0)
    
    myPath = "c:\users\xyz123\documents\vb\kiran\"  'sub in your actual path here ***
    Set objFSO = CreateObject("Scripting.filesystemobject")
    Set objFolder = objFSO.getfolder(myPath)
    For Each objFile In objFolder.Files
        If Left(objFile.Name, 3) <> "Mac" And Left(objFile.Name, 3) <> "~$M" Then
            If myFiles(0) <> "" Then
                ReDim Preserve myFiles(UBound(myFiles) + 1)
            End If
            myFiles(UBound(myFiles)) = objFile.Name
        End If
    Next
    
    For i = 0 To UBound(myFiles)
        currFile = myFiles(i)
        foundString = InStr(currFile, "exp")
        If foundString > 0 Then
            'search for corresponding "act" file
            For j = 0 To UBound(myFiles)
                If Len(myFiles(j)) = Len(currFile) And myFiles(j) <> currFile Then
                    'found a different file with same # of characters in file name
                    If Mid(myFiles(j), foundString, 3) = "act" Then
                        'found the corresponding "act"
                        Set wbext = Workbooks.Open(myPath & currFile)
                        Set wbAct = Workbooks.Open(myPath & myFiles(j))
                        'open both the Exp and Act workbooks
                    End If
                End If
            Next j
        End If
    Next i
    
    Set objFile = Nothing
    Set objFolder = Nothing
    Set objFSO = Nothing
End Sub

[akiran]

Hi,

I have tried this code, it is recursiverly opening all the workbooks in the folder and code is keep on executing without a break.

I have attached the folder for ur convenience.
It has to compare the data as I have mentioned above and update the status as pass/fail in those respective column headers for scenarios in those respective work book names prefixed.
No of scenarios and no of categories will change as per requirement.
kindly check the attached package to get clear info.

Please confirm if further info needed.

Thanks,
Kiran

[akiran]

Hi,

I am able to pull data from all the workbooks inside the folder into a single workbook. Got the below code from one of the website
Do you think this way I can compare the data and get the o/p as pass/fail on respective cells in attached template?
pls suggest me

code:

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = ThisWorkBook.path & "\"
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A1:Y19")
        
        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

[vbfbryce]

If you're to the point where you can populate the "master" from all the files in the directory, show us what that will look like after you've done so. I'm sure you can do what you're looking to do from there, but you need to show us what it will look like, and what you want the results to look like.

[akiran]

Hi,

I have attached the complete package as how I wanted.
In the attached package:
1)all files from where data to be merged are there.
2) Master Actual : This is what the o/p I got from the code which I have used to merge the data.
3) Master Expected : This is how I want the data to be merged.
4) Test result spread sheet - where it shows o/p of the data comparion from master sheet of each category as pass/fail.
Detail :
Master sheet how I am looking for :
* A Seperate sheet with category name to be created for each category and all data related to that particular category(expected and related actual results data) should be merged into those respective sheets one beside other.
* Comparison table to be created beside those two tables data where it shows pass/fail according to data match.
* if comparison is pass for all fields in that respective category ( 1, 2 ,3,4 ...) it should show PASS in the Test result template in those respective cells.
* if atleast one of the cell in the whole category is fail, it should show fail in the Test result template in that respective cell of that particular category.

Please look the attached package to understand this better.

Thanks,
Kiran

[vbfbryce]

Look at the attached screen shot.

What I think you're asking for, once you have the expected and actual side by side in the same sheet, is as follows:

For each "group" ("Expected A" and "Actual A"), you want a record on the test result sheet where I've shown it in red. If the "blocks of values," for example the blue rectangle on the left and the blue rectangle on the right, are all the same, you want a "pass" to be recorded, but if any single value is different you want a "fail."

If that's correct so far, where does this come in to play:

" Comparison table to be created beside those two tables data where it shows pass/fail according to data match."

Also, where do the "A" and "B" and "C" that I put the black rectangle around come from?

[akiran]

Hi

First thing is all the data in the tables is dummy.
let me explain to you in detail :
Test result WorkBook: abc and abcd_efgh ... are category names and scenario1, scenario2 .... will come under each category.
If we consider Scenario 1 for category "abc" -
in Master Expected WorkBook: Data in range B2:Z19 should be expected result and data in range AC2:BA18 is the actual result for scenario 1 of abc category
If all the data in B2:Z19 matches with the corresponding expected data in range AC2:BA18 then i should get pass/fail in cell E7 in testresult workbook (which belongs to scenario 1 for category ABC)
similarly
scenario 2 for category abc -
If all the data in range B22:Z39 matches with the corresponding data in range AC22:BA39 then i should get pass/fall in cell E8 in test result workbook (which belongs to scenario 2 for category ABC)
and so on .... should be repeated for all scenarios in the category ABC
and next this process to be repreated all the categories (eg: abcd_efgh ....and their respective scenarios(1,2,...))
Since my no of categories and no of scenarios for each category are dynamic it should take care of dynamic data.

Comparion table is for my reference since it shows the pass/fail for corresponding tables and I can easily locate the mismatch of data for any category under particular scenario if found for any particular cell as fail so that i can take appropriate action to correct the data.

A , B, C which you have highlighted in black are my headers and since I mentioned Test results workbook is a template those are my business related headers and there is nothing to do with them.

Please let me know if its clear for you .

Thanks,
Kiran

[vbfbryce]

What is the difference between "S. No." in column A on the test results sheet and "scenario x" in columns E, F, G, etc.?

[akiran]

Hi,

They both are different and dont relate to each other.
under scenario1 we have data populated in range B2:Z19 and s.no is part of that data header. As I told you please don't consider what the data and headers are since they are DUMMY.
Just to ensure some dummy data is there I have filled it with all those column names and data inside those columns. In real my data and all the headers will be different.

[vbfbryce]

Here is some code to start the "compare" process. One issue is that it doesn't post results across the row in the result workbook, only in column E. From your explanations in previous posts, I'm not sure exactly how the columns after E come into play.

Step through and see if this gets you close:

Sub Compares()
    Dim wbMaster As Workbook
    Dim wsMaster As Worksheet
    Dim wbResults As Workbook
    Dim wsResults As Worksheet
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim headerRow As Long
    Dim topData As Long
    Dim bottomData As Long
    Dim lastCol As Long
    Dim targetRow As Long
    Dim failed As Boolean
    
    headerRow = 1
    
    Set wbMaster = Workbooks("master.xlsx")   'change to your workbook name, and it must be open
    Set wbResults = Workbooks("testresult.xlsm")   'change to your workbook name, and it must be open also
    Set wsResults = wbResults.Worksheets("sheet1")   'assumes "sheet1" is where the results go
    
    For i = 1 To wbMaster.Sheets.Count
        Set wsMaster = wbMaster.Worksheets(i)
        While headerRow <> wsMaster.Rows.Count
            topData = headerRow + 1
            bottomData = wsMaster.Range("b" & topData).End(xlDown).Row
            lastCol = wsMaster.Range("b" & headerRow).End(xlToRight).Column
            For j = topData To bottomData
                For k = 2 To lastCol
                    If wsMaster.Cells(j, k) = wsMaster.Cells(j, lastCol + k + 1) Then
                        'match, keep going
                    Else
                        'no match, record "fail" and exit loops
                        failed = True
                        k = lastCol
                        j = bottomData
                    End If
                Next k
            Next j
            Stop
            targetRow = wsResults.Range("e" & Rows.Count).End(xlUp).Row + 1
            wsMaster.Range("a" & headerRow).Copy
            wsResults.Range("b" & targetRow).PasteSpecial
            If failed = False Then
                wsResults.Range("e" & targetRow).Value = "PASS"
            Else
                wsResults.Range("e" & targetRow).Value = "FAIL"
            End If
            failed = False
            headerRow = wsMaster.Range("a" & headerRow).End(xlDown).Row
        Wend
    Next i
End Sub

[akiran]

Hi,

Thanks a lot the time you are spending on this

If u see master expected workbook :

we have sheet tabs with category names abc, abcd_efgh ...
in abc category sheet - all the outputs for scenario 1 ( expected and actual tables data), scenario 2 , .. should be populated.
in abcd_efgh category sheet - all the outputs for scenario 1 ( expected and actual tables data), scenario 2 , .. should be populated.
if you can see attachment it contains spreadsheets with names abc_exp_1 : which means it has the data for abc category, expected result for scenario 1
like wise abc_act_1 : it has data for abc category, actual result for scenario 1
similarly for other scenarios and categories the same is applicable those workbooks have data related to that particular scenario and category.

Now I am able to merge all the data in one excel with mentioned code but not in the required format( Master actual workbook )

Now I want the merged data to be consolidated / divided into my required format (Master expected workbook).

Once data is formatted in required way - Comparison should start and result should be populated into those respective category wise and scenario wise fields in Testresult spread sheet without modifying predefined category names already populated there as abc , abcd_efgh .... since its my predefined template with category names.


and for your question

I'm not sure exactly how the columns after E come into play.

for scenario1 expected results table data and actual results table data should be compared for category ABC and result should be populated in E7 cell of testresult workbook.
for scenario2 expected results table data and actual results table data should be compared for category ABC and result should be populated in F7 cell of testresult workbook.
for scenario3 expected results table data and actual results table data should be compared for category ABC and result should be populated in G7 cell of testresult workbook.
and so on until all the scenarios (a max of 10 scenarios may exist thats where we have columns E:N in test result sheet) are compared in that particular category ABC.
same to be repeated for next category abcd_efgh for all available scenarios.
scenario1 expected results table data and actual results table data should be compared for category abcd_efgh and result should be populated in E8 cell of testresult workbook.
scenario2 expected results table data and actual results table data should be compared for category abcd_efgh and result should be populated in F8 cell of testresult workbook.

and so on ....

so that I can see all the scenarios of all the categories are compared and the result is populated on Master expected sheet.

Hope I am clear now ?

I have tested the code which you have provided and its giving different results which I am not looking for.

I need the data to be modified first according to expected master workbook and then data to be compared and then result to be posted only in range E:N in testresult workook without modifying other columns.

[vbfbryce]

This part:

"for scenario1 expected results table data and actual results table data should be compared for category ABC and result should be populated in E7 cell of testresult workbook.
for scenario2 expected results table data and actual results table data should be compared for category ABC and result should be populated in F7 cell of testresult workbook.
for scenario3 expected results table data and actual results table data should be compared for category ABC and result should be populated in G7 cell of testresult workbook."

I now understand!


so...the format of "master" that you've attached is not how you want it to be yet? What needs to be different in its formatting?

[akiran]

If you see the attached package, there are two Master workbooks - 1) Master Actual 2) Master Expected.

when I use the code which i got for some website - to merge all the excels in the folder I am getting Master Actual as output from it.
However, I need the Master Expected excel sheet as output after merging the data since it is readable and easier for comparing is what I believe.

[vbfbryce]

Assuming your data has been put into the format of Master Expected (the expected values for a scenario in a block which is next to a block containing the actual values, then the next scenario below that one), I think this will do what you're looking for:

Sub Compares()
    Dim wbMaster As Workbook
    Dim wsMaster As Worksheet
    Dim wbResults As Workbook
    Dim wsResults As Worksheet
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim headerRow As Long
    Dim topData As Long
    Dim bottomData As Long
    Dim lastCol As Long
    Dim targetRow As Long
    Dim failed As Boolean
    Dim currCategory As String
    Dim scenCount As Integer    'scenario counter
        
    Set wbMaster = Workbooks("master expected.xlsx")    'watch this name
    Set wbResults = Workbooks("testresult.xlsm")
    Set wsResults = wbResults.Worksheets("sheet1")
    
    For i = 1 To wbMaster.Sheets.Count
        headerRow = 1
        scenCount = 0
        Set wsMaster = wbMaster.Worksheets(i)
        currCategory = wsMaster.Name
        targetRow = wsResults.Range("e" & Rows.Count).End(xlUp).Row + 1
        While headerRow <> wsMaster.Rows.Count
            scenCount = scenCount + 1
            topData = headerRow + 1
            bottomData = wsMaster.Range("b" & topData).End(xlDown).Row
            lastCol = wsMaster.Range("b" & headerRow).End(xlToRight).Column
            For j = topData To bottomData
                For k = 2 To lastCol
                    If wsMaster.Cells(j, k) = wsMaster.Cells(j, lastCol + k + 1) Then
                        'match, keep going
                    Else
                        'no match, record "fail" and exit loops
                        failed = True
                        k = lastCol
                        j = bottomData
                    End If
                Next k
            Next j
            wsResults.Range("b" & targetRow).Value = currCategory
            If failed = False Then
                wsResults.Cells(targetRow, 4 + scenCount) = "PASS"
            Else
                wsResults.Cells(targetRow, 4 + scenCount) = "FAIL"
            End If
            failed = False
            headerRow = wsMaster.Range("a" & headerRow).End(xlDown).Row
        Wend
    Next i
End Sub

[akiran]

Hi,

This code is working fine but I want my data to be formatted as per Master Expected workbook first before I use this code rite?
please help me out with formattting of data from master actual workbook aswell, else if you can help me out in merging the data from all the excels in the folder as per my required format of master expected would also do(first step of gathering the data before running the above code).

[vbfbryce]

So you're saying you need to get the Expected and the Actual data side by side, right?

At some point then you need to find the "matching pairs." It can be done as you're opening the files or after you've got all the data in your summary sheet. Attached is some code which is an add-on to what I posted in #4, which finds the pairs first, then puts the data from each pair side by side in the summary sheet.

See if that gets you the format you're looking for.

[akiran]

Hi,

I have checked the code. Its working fine.
But it has to paste the related category names data into seperate sheets with category name as sheet name and also a new comparison table after exp and act for each category which shows me clearly for which cell there is difference in the values wen compared to both exp and act (as per Master expected) so that I can use your code #17 to compare the results and get the output in testresult sheet.

Set SourceRange = wsCopyFrom.Range("a1:y19") 'is this range fixed or variable? *****

for ur question in the code - Its variable range .

also it would be great if you connect both the codes so that by single click both the tasks should happen
1) preparing master expected sheet
2) populating result in testresult

Thanks a lot for all the help and time your spending :-)

[akiran]

Hi,

Any luck?

[vbfbryce]

Let's do this one step at a time. What is the first thing you need it to do? What is the input and the output of step 1?

[akiran]

First step is to prepare master expected excel work book.
1) To take data from all the seperate excels inside the folder and merge into seperate sheets according to category name
While merging - Data to be pasted side by side as per the scenario name and after that a seperate table to be created beside each scenario which should show me pass/fail each cell.
Please find the attached.

Note: I will execute the code from Test result workbook which is of predefined format with category names already available in column "B".

Only thing is once I execute the code it has to -
Step 1) Generate MasterExpected workbook with required format as attached.
Step 2) Data to be compared and o/p to be shown in Testresult workbook in range E:N(depending on no of scenarios). I only need "PASS"/"FAIL" as o/p to Test result sheet.

STEP 1 Task:
I/p will be from all the excels inside the folder.
o/p should be as per master expected workbook which is attached - It should contain seperate sheets as per category names and also while data is being merged according to scenario exp and act side by side , a seperate comparison table to be created for each scenario under each category. My intention to create this seperate comparison table is if I find any data mismatch I can exactly see the mismatch in which scenario under which cell.

[akiran]

Hi,

Were you able to understand the requirement?

[vbfbryce]

I think so, yes.

Step through the code in the attached.

One thing it does NOT do is save the "Master" book, so if you want that, you'd have to add it.

Also, note that it will make Excel invisible when it runs, then make it visible again when it's done. Take that out if you don't want it like that.

[akiran]

Hello,

Thank you soooooooooo much
This is fantastic and working as how I wanted.
You made my day coz I am struggling a lot for this code from some time now
I am really greatful for your help and for your precious time in getting this done for me...

Kiran

[vbfbryce]

You're welcome!