Advanced return values that don't match and highlight cells

[sdk1013]

I'm looking for formulae that will compare Sheet1 and Sheet2, searching Sheet2 for cells that don't match columns C-E for the associated ID and Date (columns A & B) in Sheet1. Further, I'd like to highlight in Sheet1 whatever cells in columns C-E that don't have matches in that can be found in Sheet2. Ideally, a new set of columns in Sheet1 would also return the values from both sheets separated by a "/" to show the values that don't match between sheets.

For clarity, I have an older dataset with costs of events at 3 different locations and a new dataset that is supposed to include data from the old dataset, same 3 locations. For some reason, costs have been altered in the new dataset retroactively and I need to find the ID/Dates with any discrepancies in costs at any locations, given the associated ID/Date.

Example below...

Sheet1 before algorithm applied

ID	DATE	LOCATION 1	LOCATION 2	LOCATION 3
1​	10/03/2016​	$12.60​	$34.16​	$34.16​
2​	10/03/2016​	$4.20​	$19.03​	$19.03​
6​	10/04/2016​	$12.60​	$34.16​	$34.16​
7​	10/04/2016​	$4.20​	$32.44​	$32.44​
8​	10/13/2016​	$12.60​	$34.16​	$34.16​
9​	10/13/2016​	$12.60​	$20.00​	$20.00​
10​	10/13/2016​	$12.60​	$34.16​	$34.16​
11​	10/13/2016​	$12.60​	$29.73​	$29.73​
14​	10/13/2016​	$12.60​	$34.16​	$34.16​
15​	10/13/2016​	$4.20​	$16.49​	$16.49​

Sheet2

ID	DATE	LOCATION 1	LOCATION 2	LOCATION 3
1​	10/03/2016​	$12.60​	$34.16​	$34.16​
2​	10/03/2016​	$4.20​	$19.03​	$5​
6​	10/04/2016​	$5​	$34.16​	$34.16​
7​	10/04/2016​	$4.20​	$5​	$32.44​
8​	10/13/2016​	$12.60​	$34.16​	$5​
9​	10/13/2016​	$12.60​	$20.00​	$20.00​
10​	10/13/2016​	$12.60​	$34.16​	$34.16​
11​	10/13/2016​	$12.60​	$29.73​	$29.73​
14​	10/13/2016​	$5​	$34.16​	$34.16​
15​	10/13/2016​	$4.20​	$5​	$16.49​

Sheet1 after algorithm applied

ID	DATE	LOCATION 1	LOCATION 2	LOCATION 3	LOCATION 1check	LOCATION 2check	LOCATION 3check
1​	10/03/2016​	$12.60​	$34.16​	$34.16​
2​	10/03/2016​	$4.20​	$19.03​	$19.03​			19.03 / 5
6​	10/04/2016​	$12.60​	$34.16​	$34.16​	12.60 / 5
7​	10/04/2016​	$4.20​	$32.44​	$32.44​		32.44 / 5
8​	10/13/2016​	$12.60​	$34.16​	$34.16​			34.16 / 5
9​	10/13/2016​	$12.60​	$20.00​	$20.00​
10​	10/13/2016​	$12.60​	$34.16​	$34.16​
11​	10/13/2016​	$12.60​	$29.73​	$29.73​
14​	10/13/2016​	$12.60​	$34.16​	$34.16​	12.60 / 5
15​	10/13/2016​	$4.20​	$16.49​	$16.49​		16.49 / 5

 

[RasGhul]

Hi sdk,

Maybe this suits? sumproduct will only work if the location ranges are actually number values not text.

Advanced return values that don't match and highlight_sdk.xlsx
	A	B	C	D	E
1	ID	DATE	LOCATION 1	LOCATION 2	LOCATION 3
2	1	10/03/2016	$12.60	$34.16	$34.16
3	2	10/03/2016	$4.20	$19.03	$5.00
4	6	10/04/2016	$5.00	$34.16	$34.16
5	7	10/04/2016	$4.20	$5.00	$32.44
6	8	10/13/2016	$12.60	$34.16	$5.00
7	9	10/13/2016	$12.60	$20.00	$20.00
8	10	10/13/2016	$12.60	$34.16	$34.16
9	11	10/13/2016	$12.60	$29.73	$29.73
10	14	10/13/2016	$5.00	$34.16	$34.16
11	15	10/13/2016	$4.20	$5.00	$16.49
Sheet2

Advanced return values that don't match and highlight_sdk.xlsx
	A	B	C	D	E	F	G	H
1	ID	DATE	LOCATION 1	LOCATION 2	LOCATION 3	LOCATION 1check	LOCATION 2check	LOCATION 3check
2	1	10/03/2016	$12.60	$34.16	$34.16
3	2	10/03/2016	$4.20	$19.03	$19.03			19.03 / 5.00
4	6	10/04/2016	$12.60	$34.16	$34.16	12.60 / 5.00
5	7	10/04/2016	$4.20	$32.44	$32.44		32.44 / 5.00
6	8	10/13/2016	$12.60	$34.16	$34.16			34.16 / 5.00
7	9	10/13/2016	$12.60	$20.00	$20.00
8	10	10/13/2016	$12.60	$34.16	$34.16
9	11	10/13/2016	$12.60	$29.73	$29.73
10	14	10/13/2016	$12.60	$34.16	$34.16	12.60 / 5.00
11	15	10/13/2016	$4.20	$16.49	$16.49		16.49 / 5.00
Sheet1

Cell Formulas
Range	Formula
F2:H11	F2	=IF(SUMPRODUCT((Sheet2!$A$2:$A$11=$A2)*(Sheet2!$B$2:$B$11=$B2)*(Sheet2!C$2:C$11=C$2:C$11)*Sheet2!C$2:C$11)=0,TEXT(C2,"####.00")&" / "&TEXT(SUMPRODUCT((Sheet2!$A$2:$A$11=$A2)*(Sheet2!$B$2:$B$11=$B2)*Sheet2!C$2:C$11),"####.00"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:E11	Expression	=F2<>""	text	NO