1

I am sure that there is a smarter approach then this brute one:

=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
             p40<>o40;p40<>Q40;p40<>R40;p40<>S40;p40<>T40;p40<>U40;
             q40<>P40;q40<>o40;q40<>R40;q40<>S40;q40<>T40;q40<>U40;
             r40<>P40;r40<>Q40;r40<>o40;r40<>S40;r40<>T40;r40<>U40;
             s40<>P40;s40<>Q40;s40<>R40;s40<>o40;s40<>T40;s40<>U40;
             t40<>P40;t40<>Q40;t40<>R40;t40<>S40;t40<>o40;t40<>U40;
             u40<>P40;u40<>Q40;u40<>R40;u40<>S40;u40<>T40;u40<>o40);1;2)

I tried something like this but its not working as that above:

=IF(COUNTIF({O40;P40;Q40;R40;S40;T40;U40}<>{O40;P40;Q40;R40;S40;T40;U40};{O40;P40;Q40;R40;S40;T40;U40}<>{O40;P40;Q40;R40;S40;T40;U40});1;2)

can someone briefly teache me? thx

EDIT:

explanatory: on row 40 I have 7 cells with some mixed values (numbers, laters). what I'm trying to do here is to check every cell from those 7 with every cell of those 7. so if there is no match between them then value 1 is displayed and if there is a match then value 2 displays. embeding image with colors:

enter image description here

  • You seem to be using COUNTIFS wrong. The first argument is normally a range (perhaps O40:U40?), and the list of conditions start from the 2nd one. It would also help if you wrote about the problem that you want to solve, I'm having a hard time imagining what such a convoluted COUNTIFS calculation could represent. Perhaps there is another way to solve your business problem. – ttarchala May 13 '18 at 21:08
  • @ttarchala I made an edit... yeah, I'm kinda sense that I use COUNTIF wrongly but that first formula (the long one) works flawlessly so far in my testings – user902300 May 13 '18 at 21:16
  • The formula might be giving you correct results purely by accident. Normally the 1st, 3rd, 5th etc. every odd argument to COUNTIFs must be a range, so I'm guessing the function quietly converts the results from comparison into ad-hoc ranges there. It would help if you didn't just post the formula but also test data which you are comparing and even explained the "business" meaning of these data. – ttarchala May 13 '18 at 21:29
  • @ttarchala so I should double it? like: =IF(COUNTIF(O40<>P40;O40<>P40 ; O40<>Q40;O40<>Q40...... ? well as I explained in the post: all I wana is to compare those 7 cells with each others and find out if there is not any "duplicates" – user902300 May 13 '18 at 21:44
  • For finding duplicates, it might be much more useful to apply the function UNIQUE. Try calculating =COUNT(UNIQUE(O40:U40)) and see what it yields for various test data. – ttarchala May 13 '18 at 21:46
1

To see if you have any duplicates in your range, use this expression:

=IF(ROWS(UNIQUE(TRANSPOSE(A1:G1))) = COLUMNS(A1:G1); "UNIQUE"; "DUPLICATE")

It's going to work with a range of any length. For vertical ranges, drop the TRANSPOSE and use ROWS instead of COLUMNS for comparison.

  • this solution would never cross my mind. thank you. – user902300 May 13 '18 at 22:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.