Help with a formula to output an error if all instances occuring in column A do not have the same value in column B

[mick0005]

Hey gurus -

I have a spreadsheet where we have a list of values in column A (many of which are duplicates, which is necessary) and then in Column B users will enter either a "Y", "N', or leave it blank corresponding with the value in A. I need a formula that will alert me in Column C if all of the duplicate values in column A do not match what was entered in Column B.

For example, lets say column A has 3 values:

Excel Workbook
	A	B	C
1	Mike	Y	No Match
2	Mike	N	No Match
3	Mike	Y	No Match
Sheet1

I want to be alerted if Column B's corresponding entries do not all match... in this case the options would be all "Y" all "N" or all Blank (no entry) in order to not error out. If any one of them do not match, then there would be an error of "No Match" for all entries for that given unique value from Column A.

I have included below a picture of a subset of the actual data and what I would want to see in Column C.

Ideas?

 

[mick0005]

I wanted to clarify this a bit because as I re-read it I thought maybe I wasn't as clear as I should have been.

If the duplicate values from column A's corresponding values in column B DO match, then there would be no output in column C as I have shown in the example below. I only need to be alerted if all of B's values (for a given duplicate value in column A) do not all match.

In the example below, the Mike's B values all match, so there is nothing in Column C. Tom's B value's don't match so they result in "No Match"

Excel Workbook
	A	B	C
1	Mike	Y
2	Mike	Y
3	Mike	Y
4
5	Tom	Y	No Match
6	Tom		No Match
7	Tom	N	No Match
Sheet1

 

[Marcelo Branco]

Assuming your data beginning in row 2 (headers in row 1), maybe this

Formula in C2 copied down

=IF(COUNTIF($A$2:$A$100,A2)<>COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2&""),"NO MATCH","")


HTH

M.

 

[mick0005]

Assuming your data beginning in row 2 (headers in row 1), maybe this

Formula in C2 copied down

=IF(COUNTIF($A$2:$A$100,A2)<>COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2&""),"NO MATCH","")


HTH

M.

Top Freakin' NOTCH buddy! Outstanding work. Why didn't I think about just counting the values?? That would have gotten me started, but still I am not sure I know what COUNTIFS (with the S on the end?) does... I don't know that I can decipher the part of the formula after the <>

Can you help just so I know for future reference?

Thanks again, you are a life saver!!

 

[Marcelo Branco]

Top Freakin' NOTCH buddy! Outstanding work. Why didn't I think about just counting the values?? That would have gotten me started, but still I am not sure I know what COUNTIFS (with the S on the end?) does... I don't know that I can decipher the part of the formula after the <>

Can you help just so I know for future reference?

Thanks again, you are a life saver!!

You are welcome and tks for the feedback

The reason for B2&"" is to handle the case when all entries are blank.

Simulate a situation where there are no entries for a spcefic text in column A and remove the &"" and you will see.

M.

 

[mick0005]

You are welcome and tks for the feedback

The reason for B2&"" is to handle the case when all entries are blank.

Simulate a situation where there are no entries for a spcefic text in column A and remove the &"" and you will see.

M.

Can you explain the logic in sentence format of what is happening after <> in the formula? I understand that it is saying if the counts for an A value equal the counts for the B values associated with the same A value, but I can't figure out how it is doing that.

 

[Marcelo Branco]

Can you explain the logic in sentence format of what is happening after <> in the formula? I understand that it is saying if the counts for an A value equal the counts for the B values associated with the same A value, but I can't figure out how it is doing that.

COUNTIF($A$2:$A$100,A2)
counts the number of ocurrences of A2 in column A

COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2&"")
counts the number of occurences of the pair A2 AND B2 in columns A and B

If they are not equal (<>) then some pair A and B have a different value.

Hope i made myself clear

M.

 

[mick0005]

COUNTIF($A$2:$A$100,A2)
counts the number of ocurrences of A2 in column A

COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2&"")
counts the number of occurences of the pair A2 AND B2 in columns A and B

If they are not equal (<>) then some pair A and B have a different value.

Hope i made myself clear

M.

Outstanding! Thats what I was just coming up with... figured out that COUNTIFS is using multiple criteria and it is counting the B value only where it matches the corresponding A value. Genius! It is so simple. Your logic skills are strong my friend! I'd love to shoot you a message sometime in the future next time I have a brain teaser if you'd be open to it.

Thanks!

Mike

 

[Marcelo Branco]

Outstanding! Thats what I was just coming up with... figured out that COUNTIFS is using multiple criteria and it is counting the B value only where it matches the corresponding A value. Genius! It is so simple. Your logic skills are strong my friend! I'd love to shoot you a message sometime in the future next time I have a brain teaser if you'd be open to it.

Thanks!

Mike

Mike,

Tks very much for your kind words.

Feel free to send me a message or ask for support in the future.

All the best

M.