Formula to check various criteria based on common value in row

[mnty89]

I either couldn't properly describe this to google, or wasn't looking the right way.. Basically, I'm looking for a formula, that for the given info below, will check in this type of way:

For all of the same "ID Number", are all "Dates" the same, are all "Dates2" the same, are all the "Number" the same. IF yes, then "YES", if not, then "NO. It would be an all or nothing type of result, either they all match or they all dont... If possible a further iteration of the requested formula would say which one didnt match on every value. "Dates2 Mismatch" or something like that if the field had an outlier. In the example below ID Number 555555 would be passing, and 3333333 would not since the last line holds different values..


ID Number Dates Dates2 Number
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
555555 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 3/1/2017 1/1/2017 1
3333333 4/1/2017 1/1/2018 2

 

[Eric W]

Perhaps:

	A	B	C	D	E	F
ID Number	Dates	Dates2	Number
YES
NO
	Outlier

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(A2<>A1,IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2),"YES","NO"),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)< COUNTIF(A:A,A2)/2,"Outlier","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





This assumes that the ID numbers are grouped, like your example, so that it only shows the YES/NO for the first row of a group. If not, the formula still works, but you may want to adjust which rows it displays the answer.

As far as the outliers, there's no good way to say which those are. If even one number doesn't match, ALL of the rows won't match. So to determine which rows should be flagged as outliers, I just decided that if the particular combination of values on a given row occurs less than half of the rows for the ID number, then flag it.

If you want it to show which column doesn't match, you'd probably need a different formula for each column.

Hope this helps.