Forum Discussion

Jenn Lewis's avatar
Jenn Lewis
Copper Contributor
Jun 06, 2018

Check cells for matching data and assigning values based on this

 

So here's my situation: I am creating a sheet where I have nine groupings of three cells each, where the items chosen are all contained in lists made via data validation. What I need to do is have the previous cell grouping checked to see if there is a match, and assign a list value based on this (1 if the choices are unique to previous lists, 0 if there was a previous match).  Note that the groupings of 3 will never be the same choices, they will always be unique to each other, however, they are all chosen from the same data validation table via drop-down lists.  I should also mention that these lists of 3 are selected by different people who cannot see the values for the previous selections made, so, therefore, have no clue if their choices are unique or not.

 

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 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.

Formulas & Functions
Need Help
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jun 06, 2018

    You have to check if a color is already written in a previous cell. To do so, you have to use COUNTIF and check IF that counter is above 0.

     

    IF(OR(COUNTIF(PREVIOUS_COLORS_range;NEW_COLOR_1)>0;COUNTIF(PREVIOUS_COLORS_range;NEW_COLOR_2)>0;COUNTIF(PREVIOUS_COLORS_range;NEW_COLOR_3)>0);0;1)

     

    Check the attachment if I understood right.

    • Jenn Lewis's avatar
      Jenn Lewis
      Copper Contributor
      Jun 06, 2018

      Thank you so much!  I hadn't even thought of using the COUNT function, my brain was so fizzled trying to work out IF with AND, OR, or NOT functions that I didn't think of it.  So Thank You very much! :D

      • Arul Tresoldi's avatar
        Arul Tresoldi
        Iron Contributor
        Jun 06, 2018
        Sorry for the doublepost but after I sent the first, the system didn't show it. So I thought it was somehow lost or deleted and I did it again.

        Whuups I did it again...
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jun 06, 2018

    When you insert a range of rows/columns, excel wants to know what it has to do with them (ie insert a formula). 

     

    If I'm right and I understood your datas, Group 1 will always score 1 point (compared to no other groups). Group 2 will compare with 1, and let's say Group N is compared to all 1 to N-1 Groups.

     

    So you just have to use =COUNTIF() to specify what calc it has to do with that cells.

     

    See in the attachment what I mean.

     

    You have to set an IF condition counting the previous 3 cells, asking if the first, the second or the third value is also in that cells. Locking with $ the first cell but not the last allows you to apply this check to all the previous 3 block cells.

     =IF(OR(COUNTIF($A$2:A$4;B2)>0;COUNTIF($A$2:A$4;B3)>0;COUNTIF($A$2:A$4;B4)>0);0;IF(COUNTA(B2:B4)<3;0;1))

    In the last counta function I checked if you already inserted 3 colours or not; if not, it won't start comparing cells, assigning 0. When there will be 3 colours in the B2 B3 B4 cells, it will compare with A2 A3 A4 (if you don't want this, delete the if(counta) function and insert 1 instead). Using A$4 will allow you to copypaste the formula with self adjusting to columns ZZ$4 (example), leaving locked to the Group 1 which starts in $A$2.

Resources