Comparing Multiple columns of two excel sheets

Hi Experts.
You will do me a great favour in making my life easy to help me compare two excel spread sheets to provide report on what is different to other sheets
I have attached a samples two data sheets, I always get this and my new boss is expecting this done in no time as I take a while comparing each column of spreadsheet against other, there are about 30-40 columns in my work.
They are not sorted.

How I DO:
For each column in Data1.xls, I compare with relevant column in Data2.xls (attached)
How I do is very rudimentary, I using =IF(COUNTIF(Orders!C2:C20,[Data1.xls]Orders!$O2:$O$20)=0,"No match in B","Match in B") to compare and experimenting with Index and Match and I do this for each column and in both ways

I cannot concatenate and compare as I need to file a report what columns in both Data1.xls and Data2.xls are different, how many items and differences per column.
I googled over internet and found this and was so excited only to clear format line (To show where I stand in programming)

Please Login or Register  to view this content.

I tried to add loops to count columns and do the stuff but with bare minimum programming knowledge, I could not get my head around after going through a course.

The problems with above macro, Limited to two columns and only one cell to cell example if data1, A6 has value "5" and data1, B8 has value "5" the comparison fails as it colour codes both with many thousands of data not in order, the above macro is only bit of help.
1. I want a loop which iterates from beginning to end of the list for each element to compare and then compare in reverse and then loop this for all columns
2. To get the above macro, I have to copy each column from Data2 to Data 1, by adding a column - So painful, if only I get the option to compare from a given column of Data1.xls to another column in Data2.xls.
3. Also another challenge is that columns are not in same order as Data2.xls has some columns moved across

Please can you help me, Sample files are attached.

to get started, you need to ask yourself first....do the cols have names? headers? if they do, you need to capture the returns using this function:

https://docs.microsoft.com/en-us/off...cel.range.find

using the .row property. or just assign the number 1 to ROW:

https://docs.microsoft.com/en-us/off...xcel.range.row

the return from FIND returns a RANGE object:

""A Range object that represents the first cell where that information is found.""

ranges have many thing - cols, rows, values, etc, etc.... if you just WRITE the range var in code, it returns the value of the cell:

Please Login or Register  to view this content.

that code will print columnname if it is an actual col header.

Where is my message gone?
Hi Adam, Thanks a ton. I will surely try to modify this with help you have provided. If I get struck, hope it is ok to get back.
Thanks a lot.

of course. I literally teach. and that was what my post was meant to do. I don't really provide solutions without telling people how it works and/or how to do it themselves. because that doesn't help anybody do or learn anything. If i did that, I might as well call myself Joe Biden.

Thanks a lot Adam.
May I ask a question

Say my cursor is at A1 ( Does it matter?)
Trying to understand this which counts all the rows used

Formula:

Please Login or Register  to view this content.

Cells() is an object having the method End(), which is executed first so it goes to the end, while going to the end it calculates the rows ( or while traversing back to A1 ?) so now we have total rows for column A.

So I tried this
temp1 = Cells(.Rows.Count,“A”)

Result: Always it shows up "0"
Then, assuming now that temp1 is the object of class Cells(), I tried to call the methods "

Formula:

Please Login or Register  to view this content.

"
I get "Invalid qualifier" compiler error, So obviously temp1 is not the object ?

Please can you explain me this, how is this evaluated, in debugger we have steps which is not giving me how compiler is evaluating the function ?
and can I apply same logic to all functions ?

Thanks a lot.

you need to read about the concepts of programming. these posts by me is very advanced, however it will give you at least a little bit of insight into what this whole environment is about:

https://www.excelforum.com/excel-pro...ml#post5435789

https://www.excelforum.com/excel-pro...ml#post5435792

as you can see in the image below, the code you wrote is not correct:

Please Login or Register  to view this content.

cells has 2 arguments. ROW and COLUMN. those are both numbers. not strings. ''A'' is not going to work. the rest of your words are not really correct either. sorry. =( I teach many people in other countries what this is all about, and I suppose I could put something together for you if you would like, to explain how all of these concepts work together and how easy it really is. If you put common sense to it, the programming world is really nothing more than a duplicate (or MIRROR) or the material world on Earth. It doesn't matter what language you use, what OS you use, what dev tool you use, or anything else. I can do that for you, but not today. I don't have documentation for all of this in the form of teaching material. But I do have instructions I've written for many others for VBA, PHP, Visual Studio, javascript and some others. you should really start with the following information, with regard to the high-to-low or parent-to-dependency relationship between all the programming resources in every language. here is a basic overview of how things depend on one another:

=> object
=> method
=> arguments
=> property
=> controls

Hi Adam,
Happy to learn and I really appreciate your efforts and time. Please look forward to learn and automate stuff. As I look into learning these in Object Oriented way.
Thanks a ton

Best Regards

how do you want to receive the info? I suppose I could post it here for everyone to see, but that's practical because the info I would write for you would be insightful to the point that it should be paid for. If anybody else is looking at this, they can respond as well. Send me a PM and tell me how you want to get it. and anyone else reading this, you can also look at it if you want, but I won't post it publicly. If the mods here think this is unfair, I can post a watered down version of the info in the tutorials section here. But I won't do that unless an authority says something in response to this post. But this type of info isn't really related to this forum's content anyway.

I will wait to hear from you.

protocoder,

What you currently have is that you have 2 files with same header but different order, and you need to compare those and report the details.

My question is, are you able to handle if the orders of the headers are exactly the same?
If not, how do you determine match / no match?

@jindon
Thank you for asking. These two files are from different departments and I usually do manually check relevant columns, copy these two columns in temp excel and do the Countif stuff

My question is, are you able to handle if the orders of the headers are exactly the same?
If not, how do you determine match / no mat

To be honest, I have to do manual check for each reference column header, the corresponding column from other departments. As I doing comparing as mentioned above extracting column from each to another sheet, compare and create report and do that for all the columns. Sorry not smart guy in the block
So over last day, I got to this point

Please Login or Register  to view this content.

This is working for two columns in the same way, I do copy columns from two sheets, I still have to figure out how to open worksheet ( I got that code) and thinking to create a prompt which I give as input mapping.
The other is sub module which reads columns and create array mapping ? haven't reached that expertise.

But happy to consider a solution where at least columns headers are mapping.
Thanks a ton and Best regards

match = match + 1

Are you just counting the each one of data in column throughout the other column?
I just don't understand what you are trying to do...

@Jindon

Originally Posted by jindon

Are you just counting the each one of data in column throughout the other column?
I just don't understand what you are trying to do...

That is only to create reports on how many product IDs matched & how many times with the reference, that info goes into silly power point to show case how great decisions helped increase sales of a particular product (Maybe?). But yes I am asked to collect that information

Originally Posted by jindon

So you are importing(copy/paste) from other workbook one by one...
This is to import the data from Data1 to Data2 arrainging the order of Data1 headings order to Data2 and apply conditional format.

Please Login or Register  to view this content.

@jindon, Thank you, I did couple of tests, it is working. To make it easy, I have renamed my office files Data1, Data2 with sheets "Orders" and it is working. In fact one the data suppliers had many more additional headers, even they came out in conditional format color. This really helps me in my job.
Please may I ask a favour, can you explain the flow, so that I make relevant changes where needed in future.

Thanks a ton
(arigato gozaimasu)

Originally Posted by jindon

This is to import the data from Data1 to Data2 arrainging the order of Data1 headings order to Data2 and apply conditional format.

That should explain.
If you want to ask further about the lines of the code, ask specific line(s) that you don't understand.