How do you compare two load sheets to tell you exactly what the differences are

[ac1223]

Currently the way I compare sheets is I make 2 sheets and then on the 3rd sheet I use the following formula =IF(Sheet2!A1<>Sheet1!A1,"difference",""). This makes the word difference appear on the 3rd sheet in every cell that is different between the two sheets. Does anyone know what to replace the word "difference" with in order to get more detailed info? are there any better ways to compare two load sheets and see what is updated?

 

[etaf]

depending on what you want to show - you could use
=IF(Sheet2!A1<>Sheet1!A1,"Sheet2 contents = "&Sheet2!A1&" "&"Sheet1 contents = "&Sheet1!A1,"")
to show the actual contents of the difference - if that helps

 

[AC PORTA VIA]

or macro to highlight each cell if there is difference like below.
ws1

ws2

 

[AC PORTA VIA]

here is macro option.

Sub CompareWorksheets()

For Each cell In Worksheets("WS1").UsedRange
If cell.value <> Worksheets("WS2").Range(cell.Address) Then
cell.Interior.ColorIndex = 3
cell.Font.Bold = True
cell.Font.FontStyle = "Bold Italic"
End If
Next

For Each cell In Worksheets("WS2").UsedRange
If cell.value <> Worksheets("WS1").Range(cell.Address) Then
cell.Interior.ColorIndex = 3
cell.Font.Bold = True
cell.Font.FontStyle = "Bold Italic"
End If
Next


End Sub

 

[ac1223]

Currently the way I compare sheets is I make 2 sheets and then on the 3rd sheet I use the following formula =IF(Sheet2!A1<>Sheet1!A1,"difference",""). This makes the word difference appear on the 3rd sheet in every cell that is different between the two sheets. Does anyone know what to replace the word "difference" with in order to get more detailed info? are there any better ways to compare two load sheets and see what is updated?

depending on what you want to show - you could use
=IF(Sheet2!A1<>Sheet1!A1,"Sheet2 contents = "&Sheet2!A1&" "&"Sheet1 contents = "&Sheet1!A1,"")
to show the actual contents of the difference - if that helps

Right now, this tells me the difference if the two cells are not an exact match, do you know if there is a way to compare two sheets Overall? for example

If I have one sheet that says:
Mark
Tim
Sarah

and then a second sheet that says:

Kevin
Jacob
Mark
Tim
Sarah
Todd

If these were two separate sheets, is there a way for excel to catch every difference if the data is not in a matching cell? (sorry if this doesn't make sense)

 

[AhoyNC]

Maybe something like this:
Sheet 1

Book2
	A	B
1	List1	List 2 not in List 1
2	Mark	Kevin
3	Tim	Jacob
4	Sarah	Todd
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=FILTER(Sheet2!A2:A7,COUNTIF(A2:A4,Sheet2!A2:A7)=0,"All Match")
Dynamic array formulas.

Sheet 2

Book2
	A	B
1	List2	List 1 not in list 2
2	Kevin	All Match
3	Jacob
4	Mark
5	Tim
6	Sarah
7	Todd
Sheet2

Cell Formulas
Range	Formula
B2	B2	=FILTER(Sheet1!A2:A4,COUNTIF(Sheet2!A2:A7,Sheet1!A2:A4)=0,"All Match")