Comparison 1 vs 2,3,4,5,6

[EXLent]

I see others needing comparison betweens rows or columns. I also see compare of 2 sheets or files.
Not sure which would work for what I want/need.

I have sheet1/file1 that I need to compare to sheet2/file2, 3, 4 and etc.
I was thinking if data from file1/sheet1 doesn't have a match with any than I would get a msgbox.

So is there a function for comparison or just use macro?
I have example I would like to have added. Example is only has 5 data. I want something that would be less coding as if I have 100 data.
comparison.xlsx

[Leith Ross]

Hello EXLent,

To help you with this problem, you would need to post your actual workbook. We need to see how the data is arranged, what formulas you are using, etc. Otherwise, we are just guessing at what you need.

Please provide before and after examples of your data along with any notes you would like to include.

If your workbook contains any sensitive information, please redact that information before you post.

[EXLent]

Well, that's the issue that I have. I need to know how before I can start one.
The attached was example how I wanted to work like.

I will have 6 xls files(1 compare to 5). With coding would be 6xls+1xls=7 files total.

I will be coding 7.xls to compare/match
1.xls to 2.xls
1.xls to 3.xls
1.xls to 4.xls
1.xls to 5.xls
1.xls to 6.xls

user will have all xls open and click on a button to run macro in 7.xls. When there's a match, it will mark(X) on appropriate cell. As shown on example file.

Or

I can grab data from xls file and input column A(1.xls),B(2.xls),C and etc.
and compare the columns. Not sure which is easier to understand or shorter in code.

[Leith Ross]

Hello EXLent,

I am not sure which columns of which worksheet you want to compare.

Will case matter when comparing the data?

[EXLent]

no, I just want to see coding and take it from there and edit it.

[Leith Ross]

Hello EXLent,

I can write a macro that compares column "A" of the first sheet in each workbook against column "A" in the 7th workbook.

Is that something you can use?

[EXLent]

Yep, that should work and I can take it from there.

[EXLent]

Kinda want something like this.

Compare cell A2 of 1.xls to column A of 2.xls, if Yes, X on B2 in 1.xls. If No, next cell. If yes, X on C2 in 1.xls

Does that make sense?

[Leith Ross]

Hello EXLent,

So the Workbook names would be in row 1 and the data to compare in Column "A" of "7.xls" ?

[EXLent]

Here's all the files to explain better.
2.xlsx
1.xlsx
3.xlsx
7.xlsm

[EXLent]

saw a posting with something like this =IF(ISERROR(MATCH(A1,$B:$B,0)),0,"Match")
but not sure what it really does.

[Leith Ross]

This macro will use the data in "1.xlsx" and fill in the table on "Sheet1" of "1.xlsx". The column headers are the file names.

The macro assumes you know the names of the xlsx files that are open. The Select Case statement is where the names are entered.

This macro can be placed in another workbook and run.

Please Login or Register  to view this content.

[EXLent]

what does this mean?
"Object variable or with block variable not set"?
\1