Identify differences in two sheets

[LoveLace85]

Hi everybody,
I am struggling with this case.
Every week I have a report with updated data and I would like to find easily the difference with the previous one.

So the point is I would like to have a comparison row by row from column A to column F (here semplified in two columns).

For example

File 1:

3	A
3	F
5	A

<tbody>
</tbody>

File 2:

3	A
2	4
3	F
5	A

<tbody>
</tbody>

I would like to have a macro where the 2 rows is marked or printed out.
Any help is much appreciated

 

[RasGhul]

Hi,

Here's a possible formula version if it suits;

• Apply the formula in the new file
• Highlight Columns A,B & C
• Conditional Format/New Rule/Use a formula to determine which cells/Format Values where this formula is true

=$C1="New" format to fill or Font color as you like

• You can then filter your data by "New" in the C column and print as required.

Book1
	A	B
1	3	A
2	3	F
3	5	A
Sheet1

Book1
	A	B	C
1	3	A
2	2	4	New
3	3	F
4	5	A
Sheet1

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(MATCH(A1&B1,INDEX('[File 1.xlsx]Sheet1'!$A$1:$A$3&'[File 1.xlsx]Sheet1'!$B$1:$B$3,),0)),"","New")

 

[LoveLace85]

Hi,
thank you but I wasn't able to adapt the formula you wrote me to my table.
What I did was putting th two files into 1 as different sheets, having the new table in the sheet 2

1. CONCAT(A1:F1) in H1
2. CONCAT(Sheet1!A1:F1) in I1
3. NOT(ISERROR(MATCH(H1,I:I,0)))
4. filter the "FALSE" value

What do you think? Is there a quicker method?

In addition, I am willing to learn more about vba. Is there anyone that has an idea how I could do the same with a macro?

Thank you in advance

 

[LoveLace85]

And now I was thinking that indeed my system fails a bit because it will not detect if the row has been added or modified.

 

[Joe4]

To me, this task is accomoplished much easier using Microsoft Access by using two Unmacthed Queries (one going each way), when joining the the two files/tables omn all five fields.
It will list out all records in each file/table that do not have a matching record in the other (and it doesn't really care if rows have been removed or inserted).