Formula to check various criteria based on common value in row

M

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Perhaps:

ABCDEF
ID NumberDatesDates2Number
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
619
Peter_SSs
Peter_SSs
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
566
Peter_SSs
Peter_SSs
A
  • Solved
Grabbing the latest data from a Column based on a cell
Replies
4
Views
468
PeteWright
PeteWright
B
  • Solved
Looking to expand tool to track vacation pro-rations
Replies
1
Views
264
bh24524
B
S
  • Solved
Get table name on worksheet using formulas without using VBA
2
Replies
11
Views
1K
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top