Which rows have the same value in Column A and a certain value in Column B?

[ksmmm]

I have two columns, one is an ID# for a particular institution submitting data and the other indicates the type of institution level. An institution might have submitted more than once and each submission is a new row. I want to know if each time the institution submitted, the data is missing (99) for the institution.

For example:

For every time the ID# is the same below, I only want to know whether level values for all are also '99" (including if the ID# appears only once).

ID# Level

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]61559[/TD]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61559[/TD]
[TD="class: xl65, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61559[/TD]
[TD="class: xl65, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61559[/TD]
[TD="class: xl65, align: right"]99[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61598[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61598[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61598[/TD]
[TD="class: xl65, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61598[/TD]
[TD="class: xl65, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61598[/TD]
[TD="class: xl65, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61649[/TD]
[TD="class: xl65, align: right"]99[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]61649[/TD]
[TD="class: xl65, align: right"]99[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[Eric W]

Welcome to the forum.

Maybe:

	A	B	C
ID	Level
All levels for this ID are 99.

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

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

[TD="align: center"]2[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]61649[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]61649[/TD]
[TD="align: right"]99[/TD]

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(COUNTIF($A$2:$A2,A2)=1,IF(COUNTIF($A:$A,A2)=COUNTIFS($A:$A,A2,$B:$B,99),"All levels for this ID are 99.",""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I made it so that the message only appears for the first instance of the ID, but that's easily changed if you want.

 

[ksmmm]

Thank you very much! This worked perfectly. I appreciate the help.

Welcome to the forum.

Maybe:

	A	B	C
ID	Level
All levels for this ID are 99.

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

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

[TD="align: center"]2[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]61559[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]61598[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]61649[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]61649[/TD]
[TD="align: right"]99[/TD]

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(COUNTIF($A$2:$A2,A2)=1,IF(COUNTIF($A:$A,A2)=COUNTIFS($A:$A,A2,$B:$B,99),"All levels for this ID are 99.",""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I made it so that the message only appears for the first instance of the ID, but that's easily changed if you want.

 

[Eric W]

Glad to help!

Thanks for the feedback.