Row Data Compare

[blueolive]

Hello Friends,
I want to compare two subsequent rows and highlight all the differences.

Example - Treat source A row as the correct data. If source B row data mismatches source A row, highlight it automatically as shown. There are about 60 values to compare (62 columns) and about 20,000 rows in the excel.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]employee_id[/TD]
[TD]salary[/TD]
[TD]department[/TD]
[/TR]
[TR]
[TD]source A[/TD]
[TD]100[/TD]
[TD]300[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]source B
[/TD]
[TD]100
[/TD]
[TD]350[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]source A[/TD]
[TD]230[/TD]
[TD]400[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]source B[/TD]
[TD]230[/TD]
[TD]400
[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]source A[/TD]
[TD]500[/TD]
[TD]550[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]source B[/TD]
[TD]500[/TD]
[TD]590[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]source A[/TD]
[TD]400[/TD]
[TD]490[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]source B[/TD]
[TD]400[/TD]
[TD]460
[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]


I'm using MS Office 2016.

Thank you!

 

[Peter_SSs]

You can do that with Conditional Formatting.

Assuming that data starts at the top left of the sheet per my screen shot below ..
1. Select from C2 to the bottom of your data
2. Home ribbon tab -> Conditional Formatting -> New Rule .. -> Use a formula to determine which cells to format -> Format values where this formula is true: =AND($A2="source B",C2<>C1) -> Format... -> Font tab -> Choose font color -> OK -> OK

Excel Workbook
	A	B	C	D
1		employee_id	salary	department
2	source A	100	300	A
3	source B	100	350	A
4	source A	230	400	D
5	source B	230	400	E
6	source A	500	550	T
7	source B	500	590	T
8	source A	400	490	W
9	source B	400	460	E
10
Compare

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND($A2="source B",C2<>C1)	Abc

 

[blueolive]

Thank you Peter for a quick response. I appreciate it! (sorry for a double post but I couldn't see my first post on my dashboard, whereas I could see my second post)
The solution you suggested works. My only gripe is I have 60 columns to compare so I would have to apply this conditional formatting 60 times. Any easy way to automate it?

Thank you!

 

[Peter_SSs]

My only gripe is I have 60 columns to compare so I would have to apply this conditional formatting 60 times. Any easy way to automate it?

I didn't make it clear enough in my earlier post. If you select from C2 to the bottom right of your data (that is, all 60 columns that need to be checked), then you just apply the CF formula as shown and Excel will adjust for all the other columns automatically.

 

[blueolive]

I didn't make it clear enough in my earlier post.

You did. It was my oversight.

This worked and thank you so much for a quick help!

 

[Peter_SSs]

This worked and thank you so much for a quick help!

You are very welcome.