So here's my situation: I am creating a sheet where I have 9 groupings of 3 lists where the items chosen are all contained in lists made via data validation. What I need to do is add a score to each of these groups of 3, where if the 3 choices made from the data validation list are unique from the choices made in previous groupings of 3 (Note that the groupings of 3 will never be the same choices, they will always be unique to each other).
Basically, say in group 1 you have blue, green, and purple - giving the value of 1 as the choices are unique. In group 2 you have orange, yellow, and pink - giving it a value of 1 also as the choices are unique. So on and so forth, however, say in group 9 you have magenta, red, and purple - this group will score 0 as it is not completely unique (purple was chosen in group 1). Note that choices are made at different times, and scored at these separate intervals - so where group 2 only compares to group 1, group 3 compares to both group 1 & 2, and so on.
I tried a formula similar to:
=IF(NOT(B1:B3=A1:A3),1,0)
However, the cell displayed a score of #VALUE!
instead of the 1 or 0. I'm wondering if I need to do each cell individually, so if the contents of cell B1 is not equal to the contents in A1, A2, or A3 then check the contents of cell B2 and if they are not equal to the contents of cells A1, A2, or A3 then check the contents of cell B3 and if they are not equal to the contents of A1:A3 then the value of that list is 1. If I do have to do this individually, any ideas how I would word the formula? Would it be something like:
=IF(NOT(A1:A3=B1,NOT(A1:A3=B2,NOT(A1:A3=B3),1,0)))
Is it even possible to do this in excel?
I am using the most current version of excel (office 365) with windows 10. Thank you in advance for any help.