Conditional Format Unique ROWS

[fgmoon353]

Doing a conditional format for unique cells is pretty easy to find, but I need to find unique series of rows.

Here's my set up; I have a master routing list for booking loads, columns A,B,C,D are from, to, product, trucking co.

at the end of the day I run a report which shows this and I want to compare it to the master routing list.

As an added bonus, can it conditionally format ONLY the offending (unique) cell which makes it wrong?

 

[fgmoon353]

Anything?

Thanks!

 

[Marcelo Branco]

A small data sample (~10 rows) along with expected results would be helpful.

M.

 

[fgmoon353]

[TABLE="width: 744"]
<colgroup><col span="4"><col><col span="4"></colgroup><tbody>[TR]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]Product[/TD]
[TD]Carrier[/TD]
[TD][/TD]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]Product[/TD]
[TD]Carrier[/TD]
[/TR]
[TR]
[TD]NYC[/TD]
[TD]CHICAGO[/TD]
[TD]Eggs[/TD]
[TD]Joe's[/TD]
[TD][/TD]
[TD]NYC[/TD]
[TD]CHICAGO[/TD]
[TD]Eggs[/TD]
[TD]Joe's[/TD]
[/TR]
[TR]
[TD]NYC[/TD]
[TD]PITTS[/TD]
[TD]Milk[/TD]
[TD]Bob's[/TD]
[TD][/TD]
[TD]ATL[/TD]
[TD]NASHVILLE[/TD]
[TD]Eggs[/TD]
[TD]Bob's[/TD]
[/TR]
[TR]
[TD]NYC[/TD]
[TD]DC[/TD]
[TD]Bread[/TD]
[TD]Leon's[/TD]
[TD][/TD]
[TD]MIAMI[/TD]
[TD]LA[/TD]
[TD]Eggs[/TD]
[TD]Julio's[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]NASHVILLE[/TD]
[TD]Eggs[/TD]
[TD]Bob's[/TD]
[TD][/TD]
[TD]NYC[/TD]
[TD]DC[/TD]
[TD]Bread[/TD]
[TD]Leon's[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]ST LOUIS[/TD]
[TD]Milk[/TD]
[TD]Leon's[/TD]
[TD][/TD]
[TD]ATL[/TD]
[TD]ST LOUIS[/TD]
[TD]Milk[/TD]
[TD]Leon's[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD]MEMPHIS[/TD]
[TD]Bread[/TD]
[TD]Ralph's[/TD]
[TD][/TD]
[TD]MIAMI[/TD]
[TD]ST LOUIS[/TD]
[TD]Milk[/TD]
[TD]Julio's[/TD]
[/TR]
[TR]
[TD]MIAMI[/TD]
[TD]LA[/TD]
[TD]Eggs[/TD]
[TD]Julio's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIAMI[/TD]
[TD]DALLAS[/TD]
[TD]Milk[/TD]
[TD]Julio's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIAMI[/TD]
[TD]OKC[/TD]
[TD]Bread[/TD]
[TD]Bob's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I wish I could attach an excel file! but this is the best I can do as I'm not familiar with this site. The left four col is the master sheet. The next four is the daily report. The last line would need to be conditionally formated as it doesn't match all four cells to any of the same four cells in a row. In other words, it was scheduled to go to St. Louis when to was supposed to go to Dallas.

 

[Marcelo Branco]

Assuming
Data in sheet Master in columns A:D
Data in the other sheet in columns A:D; headers in row 1; data beginning in row 2

Select A2:Dx (x = last row with data)

In Conditional formatting use this formula (adjust the ranges to suit)
=AND($A2<>"",COUNTIFS(Master!$A$2:$A$100,$A2,Master!$B$2:$B$100,$B2,Master!$C$2:$C$100,$C2,Master!$D$2:$D$100,$D2)=0)

pick the format you want (Font --> red)

Hope this helps

M.