• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparison of 2 excel workbooks

D

dereynek

New Member
Hi everyone!! first time posting here so bear with me



I’m trying to compare worksheet “listing” from workbook A and worksheet “entries” from Workbook B . Specifically, i’m comparing the sum of the data in the above mentioned. Now Here is the tricky part. I only want to sum of the cells that contain the specific text “RED” in workbookA and “PAGE” in workbook B.



Basically, I want to automate this function using VBA so that by clicking on the macro, a message box pops out telling me if the values between the sheets match or don’t match. The automation is done in a separate master file



I tried using sum(sumifs) function, but it does not seem to work for me:((



I’d be so so grateful if any of you could take the time to help me out!!! :)
 
dereynek
Are You writing about three files,
which You haven't upload here to see,
what/where are those which needs to compare?
>> Create sample files, which has enough sample values and upload those here >>
? How to sum cells, which contains the specific text “RED” or “PAGE” ?
 
hello! i’ll try to upload the files later as i’m on mobile. I tried using sum(sumif) function to sum values in the worksheet that only contains the text “RED”. this works as a normal excel function but i’m not sure how to implement this as a vba code/macro
 
Last edited by a moderator:
dereynek
hmm?
Isn't RED text different than text "RED" ?
Do You mean values (eg 1), which looks red (eg 1 ) for some reason?
How about those "PAGE"s?
 
hey!! sorry for the confusion, i have attached the excel files for more clarity.

Basically the transaction codes in wb1 match to wb 2. (Eg: RED matches to PAGES). This mapping table is written in the masterfile, where the comparison is done. All i want to do is implement a macro that will auto compare the sum of the amounts listed in wb1 and wb2 and tell me if it's a match using the Msgbox function.
 

Attachments

  • Workbook2.xlsx
    8.7 KB · Views: 4
  • MASTERFILE.xlsx
    8.5 KB · Views: 3
  • Workbook1.xlsx
    8.7 KB · Views: 3
Last edited by a moderator:
Hello!! This is actually just snippet of the data, i have to do the same for ALOT of files so i was hoping to use a macro to automate the comparison. The sumif function works perfectly on any active cell in the masterfile...but i don't want to know the total values, i just want to see if the totals match with each other in the message box....

This is the code used in vba...but no avail
Code: 
Sub Compare()      
   Wb2 = "=SUM(SUMIF([Workbook2.xlsx]Sheet1!R2C1:R10C1,""PAGES"",[Workbook2.xlsx]Sheet1!R2C2:R10C2))"           
Wb1 = "=SUM(SUMIF([Workbook1.xlsx]Sheet1!R2C1:R10C1,{""RED"",""SW-OUT""},[Workbook1.xlsx]Sheet1!R2C2:R10C2))"
If Wb2 = Wb1 Then MsgBox "Match!"
Else: MsgBox "No Match"
End If
End Sub
 
Last edited by a moderator:
dereynek
Above (#7) You try to compare again something else that You have written ... hmm? (compare PAGES with RED,SW-OUT ... hmm?)
Your 'MasterFile' would give even different image.
I tried 'something' with ... formulas.
Before any coding, You should know what?
 

Attachments

  • MASTERFILE.xlsx
    10.9 KB · Views: 2
Hey thanks for your reply:)

Yup, that's why there is a mapping table in masterfile (the words are basically acronyms haha soz if it's confusing). EG: Pages is the same transaction as RED and SW-OUT, but just generated in diff reports. I tried the same formulas as u did, and it worked for me only in the active cell. I'm trying to use the formula in vba so i can create a macro in masterfile to auto compare wb1 and wb2
 
Last edited by a moderator:
dereynek
I didn't get Your 'Yup' ... nor ... 'haha' ... in one language that word means 'mother'... hmm?

You tried something different with Your written 'code'!
If You would like to use something as 'mine formulas'
then You should know that Excel has grammar functions as well as with VBA - how to write code.
Your texts ( ... Wb1 & Wb2 ) don't follow that grammar.
>> I didn't get an idea, was my formula-based correct or something else.
>> Are those 'mapping tables' always in same place?
>> Are those files always in same folder with 'MasterFile'?
>> Many details should know ... before any code.
 
oh sorry, yup is just “yes” and haha is basically “lol”
1) yes your formula worked perfectly! but i’m trying to input this into vba (ie use a code) to generate a message box that will say “Match” if the total sum in wb1 is the same as wb2
2) Yes,mapping tables are always in the same place
3)Yes, files always in the same folder as master file

I apologise if it’s confusing...as you can see i’m clearly confused myself :(
But thank you for taking the time to reply :)
 
dereynek
Questions needs Answers to Questions to help You.
My way made sample:
Press [ Compare ]-button
 

Attachments

  • MASTERFILE.xlsb
    17.3 KB · Views: 9
vletm said:
dereynek
Questions needs Answers to Questions to help You.
My way made sample:
Press [ Compare ]-button
Click to expand...
Hello thank you so much for taking the time to reply!! But could you kindly explain the Dim maps (4,1) portion onwards? im trying to modify the values to fit into other workbooks but i don’t seem to understands the maps part. Thanks!!
 
Thank you for the reply :)
Can i just check one more thing, regarding workbook and worksheet names with hyphens and spaces

The names of my workbook and worksheet as below:

Eg: Wb = Books-Raw Listing.xls
ws = Raw Listing. xls
My code goes something like
Wb= Application.worksheet.function.Sumif(“‘[Books-Raw listing.xls]Raw Listing!E1:A1865”).........


This can’t seem to work for me^
 
Last edited by a moderator:
dereynek
You're right ... those cannot work!

Wb = Books-Raw Listing.xls means that variable Wb is same as Books-Raw Listing.xls , it cannot work!
as well as Your next ws... hmm?

Screenshot 2019-07-25 at 11.02.22.png
You could notice that from Your text ...
After You have written Your code and pressed <Enter>
Some words should begin with capital-letter.
Your 'text' has .worksheet.function.Sumif ... that should be different!
Maybe You try to use WorksheetFunction which name is SumIf or how?

Could You try to compare 'Your version' with 'My version'?
You should notice differences.
There is clear 'grammar' with Excel, how to write those codes!
 
Yes, I’m so sorry made a mistake while writing the code, i did use Application.WorksheetFunction.SumIf but it didn’t work
do you know what what i can do to resolve the error deriving from the workbook and worksheet names?

I can’t seem to reference the workbook if the name has hyphens or spaces in between them! I tried
“‘Books-Raw Listing.xls’” but it’s not working either
 
Last edited by a moderator:
One more time...
Did You compare Your 'writings' with my version?
Why You cannot use something same which I have already done?
If You add "''""''" those without clear logic - the result would be whatever! ... normally won't work at all!
If You try to write code as VBA then You should write it as it needs.
If You try to write code as with formulas with VBA then You should write it as it needs.
If You try to write formulas to sheet then You should write it as it needs.
In every case, there are clear 'grammar' how to do those.

>> I tried something like You maybe try to do ... but it has own challenges ... it could work if if and if = not stable nor good!
>> Do as I've done ... then You'll get something
 
Yes, but it doesn’t seem to work for me I changed the workbook and sheet names to the ones mentioned above so i was wondering that was why it didn’t work
 
Last edited by a moderator:
dereynek
Where have I used something like below?
Wb= Application.worksheet.function.Sumif(“‘[Books-Raw listing.xls]Raw Listing!E1:A1865”).........
You could modify those need workbook-names as well sheet-names to my code and ... smile.
 
No i think you are misunderstood. I will try to upload a file once i get home but i keep getting a run-time error ‘9’ subscript out of range.... thanks for helping anyway :)
 
Last edited by a moderator:
dereynek
As I wrote ... I tried something like You maybe try to do
You should OPEN that file as I've done - before You can do something!
or Your should write Your code with correct way!
 
Yes, i opened and used the same file as you but i needed to change the workbook and sheet names as mentioned before...i’m really new to this so i apologise for the bombarding of questions
 
Last edited by a moderator:
dereynek
Why You didn't ask that part to do with same time?
You confirm that sheet is as it is now...
but same time there could be those other file-names and so...
that it could do 'ALL NEED' with one press of button.

Questions are always welcome
... but You should sometimes even test to do as someone has given an answer!

Do You need help or something else?
 
hello!! apologize for the late reply, but i've been trying to implement your code for my other workbooks (executing the same comparison function) but keeps having error...i tried to modify your code (change wb names and maps) but still doesn't work...
I would very much appreciate if you could kindly see where im making the mistake
 

Attachments

  • MASTERFILE.xlsx
    8.6 KB · Views: 3
  • Books-Raw Listing.xlsx
    8.8 KB · Views: 0
  • Raw Listing.xlsx
    8.8 KB · Views: 1
Last edited by a moderator:
You must log in or register to reply here.
Back
Top