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

K

ksmmm

New Member
Joined
Aug 15, 2017
Messages
2
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Welcome to the forum.

Maybe:

ABC
IDLevel
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.
 
Last edited:
Upvote 0
Thank you very much! This worked perfectly. I appreciate the help.


Eric W said:
Welcome to the forum.

Maybe:

ABC
IDLevel
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
855
dreid1011
dreid1011
J
  • Question Question
Not sure if Excel can do this...
Replies
1
Views
736
Sam_D_Ben
S
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
357
MARZIOTULLIO
M
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
681
mumps
mumps
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
193
Denny57
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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