0

Hi this is my first post here, sorry for my bad english.

I'd like to see if all the age of one value are the same. If they is no duplicate or if the values are the same, then it's ok to me. In the example below, when it's "NOT OK", i'd like to copy my row, then paste it in another sheet (I can deal with this part ^^)

ID   Age   My Value
-------------------
1    15    NOT OK    
2    50    OK
2    50    OK
3    35    OK
1    16    NOT OK
1    15    NOT OK

Thanks in advance

Verd'O
  • 101
  • 1
  • 10

1 Answers1

0

You can compare a COUNTIF that simply counts based on the ID to a COUNTIFS which counts based on ID and Age:

=IF(COUNTIF($A:$A,$A2)=COUNTIFS($A:$A,$A2,$B:$B,$B2),"OK","NOT OK")

So that if the total number of entries for each ID matches the number of entries with the same age, it will show "OK".

Jordan
  • 4,424
  • 2
  • 18
  • 32
  • Not sure if you need the countif check, `=IF(COUNTIFS($A$1:$A$100,$A1,$B$1:$B$100,$B1)>1,"Not OK","OK")` – Nathan_Sav Oct 14 '16 at 09:27
  • Nathan: it doean't work if you don't use the countif check. Jordan: Thanks a lot sir! – Verd'O Oct 14 '16 at 09:35
  • @Nathan I think @OP wants to know whether the total number of IDs matches the number of IDs for the age in the row, if you remove the `COUNTIF` you won't be comparing the two and will just find out if the number of IDs for the given age is more than 1 – Jordan Oct 14 '16 at 09:40
  • Not sure, the 1 and 16 being not OK, threw me, is 15 or 16, the OK age, 1 and 16 by logic should be OK. I think, it's still early :) – Nathan_Sav Oct 14 '16 at 09:47
  • My bad, I misspoke : i want to see if the age with same ID are the same. If they aren't, i like to identify with are equal and wich are not :) – Verd'O Oct 14 '16 at 12:21