1

Topic: To compare two files Excel

Evening kind.
Help, please. Or stick with a nose where to read or search. Google gives not that.
There is a necessity to compare two files Excel. Files identical, each of them contains 47 sheets (can be more). On sheets the formatted, ready tables for the press. Tables different are small, and is that are printed on 4 sheets of format 4.
How to check up these tables? Digits should be identical
It is possible to make so: we create a similar empty file 3, in each table we write fajl1-fajl2, we copy on all sheets and we look where more than 0.
But these files will be much (the annual report in state enterprise) and time has not enough.
There is a thought to create in 3 one more sheet and in two cells to register a way to 1 and 2. And at change of a way in these cells - the data is enumerated. But I can not create the such. I interpose a hyperlink, and it further in formulas is not read.
Really such to make without using VBA?

2

Re: To compare two files Excel

Mokk;
And what sense to have two identical files - so identical, what they can be checked on "fajl1-fajl2"?
Let's admit, found out distinction - that you will do? Where it is correct, and where is not present? If you are assured, what one of files correct - why simply not to copy the correct file in another?
There is a thought to create in 3 one more sheet and in two cells to register a way to 1 and 2. And at change of a way in these cells - the data is enumerated
Look at function . But it does not work with the closed files.
It is possible to change simply a way to formulas by means of search-replacement (Ctrl+H).

3

Re: To compare two files Excel

Thanks for the answer.
Well, sense this such. It is not considered, not by me it is invented. Simply there is a desire to help one person. I with these tab. do not work. While a far-fetched problem with Excel
looked. With this function I am not on friendly terms. Accordingly did not understand, how to me it to make. Though (fairly) read also examples looked. I can a question is incorrect delivered.
There is in a cell a formula: = ' C:\Users\Oleg\Desktop \[Pere0_16.xls]  '! I8 - ' C:\Users\Oleg\Desktop \[Pere1_16.xls]  '! I9
And here this formula will be in all cells of all tables, well the title of sheet and cell number will change only. How to carry out here this part ' C:\Users\Oleg\Desktop \[Pere0_16.xls]?
Well, that changing this way all formulas were enumerated? To leave from to "find and"replace"

4

Re: To compare two files Excel

Mokk wrote:

There is a necessity to compare two files Excel. Files identical, each of them contains 47 sheets (can be more). On sheets the formatted, ready tables for the press. Tables different are small, and is that are printed on 4 sheets of format 4.
How to check up these tables? Digits should be identical

Question the first - files should be completely identical? Or it is possible that sheets are named differently, or have different height of "cap", additional columns, in one formulas, and in other values...?
The question of the second - what is required result? Ascertaining what are various? The list of sheets or cells. Which do not coincide? Illumination of different cells? Something else?

5

Re: To compare two files Excel

Akina wrote:

it is passed...
Question the first - files should be completely identical? Or it is possible that sheets are named differently, or have different height of "cap", additional columns, in one formulas, and in other values...?
The question of the second - what is required result? Ascertaining what are various? The list of sheets or cells. Which do not coincide? Illumination of different cells? Something else?

On the first question:
1. Files should be completely identical. More truly not files, and digits in tables. If numbers do not coincide, manually  source codes and search for mismatches.
2. Sheets should have identical value. Playfulness of users while it is eliminated.
3. Caps will have, probably, different height. Columns of others should not be, Probably column superfluous on the right or to the left of the table. Why probably: the File of users is formed by a certain program, I IT YET DID NOT SEE, written 10 years ago. The programmer just now  reports. It works on 0,25 rates (state enterprise). IT took this (roughly speaking mine) Exel a file and tries to make in a similar type reports. On what he writes that program I do not know. And so he declared to all of them that is fast one button all reports will be ready. All of them in ecstasy also wait for coming. And my wife (accordingly) should force down all it together and the Apocalypse waits. Errors there will be a sea. Simply already one report he prepared - 1,5 years jambs .
4. In this file there should be values. BUT!!!!! Users ADJUST numbers (there were cases) and can hammer in the formula.
3. In my file continuous formulas. In core . The data undertakes from the same basis. I am not connected to basis, and it is stupid through IBExpert + firebird I copy.
On the second question:
If are identical - norms., if distinctions - that a cell. Sheets fans. Without a difference, color, a font, filling.

6

Re: To compare two files Excel

I would go by the way of unit writing (in Excel them name macroes) in a separate file. Stupidly we sort out all sheets in two specified (open) files, and on each sheet - all filled cells, comparing Cell. Value. All mismatch we write to the protocol.

7

Re: To compare two files Excel

About macroes I understood that is possible. And without macroes in any way?

8

Re: To compare two files Excel

Yes easily... Normal conditional formatting on a cell inequality in the current book and a cell with the same address in other book. Simply it is necessary to install this formatting for each sheet of each book. However, it can be made and a macro which can be written down a macrorecorder and slightly  for autosubstitution of a name of sheet.

9

Re: To compare two files Excel

Akina wrote:

Yes easily... Normal conditional formatting on a cell inequality in the current book and a cell with the same address in other book. Simply it is necessary to install this formatting for each sheet of each book.

NOT absolutely understood as? Manually to register in the current book with what it to compare? So these books will be 27 on the one hand. In everyone to register? So is easier 3 = fajl1-fajl2. Or I not understood that?

10

Re: To compare two files Excel

Mokk wrote:

Manually to register in the current book with what it to compare?

Well generally the macro can request, what files to compare and in what of them to include conditional formatting. But it all the same VBA-programming? So it is better to do at once normally.

Mokk wrote:

So is easier 3 = fajl1-fajl2. Or I not understood that?

It is possible and so. But problems remain all the same.

11

Re: To compare two files Excel

Akina;
I understood you. Thanks for the answer. But there are small BUT, even two:
1. I never used VBA though thought to learn visit very often. Simply I by the nature of the activity do not use a computer, it is not necessary to me, well not including the Internet for search . Earlier yes, operation has been connected to a computer and Excel including.
2. Files created for the wife. And there is much worse. It is necessary pushed the button and all.
And all the same, the question remained is opened. It is possible to make somehow without programming the such:
There is in a cell a formula: = ' C:\Users\Oleg\Desktop \[Pere0_16.xls]  '! I8 - ' C:\Users\Oleg\Desktop \[Pere1_16.xls]  '! I9
And here this formula will be in all cells of all tables, well the title of sheet and cell number will change only. How to carry out here this part ' C:\Users\Oleg\Desktop \[Pere0_16.xls]?
Well, that changing this way all formulas were enumerated? To leave from to "find and"replace"
I do not know, can through LINK or similar. At me it does not turn out yet.

12

Re: To compare two files Excel

For link formation use function . Its argument (the full address which should be transformed to the link) you collect from two parts. The first (a name of sheet and the book with volume and the directory) undertakes from the fixed cell of the auxiliary sheet. The second (cell address) gathers with usage of standard functions the LINE and the COLUMN (or it is simple RC if addressing is used R1C1).