Forum Discussion

Alexander Fries's avatar
Alexander Fries
Copper Contributor
Jul 14, 2018

Comparing the data of 2 columns

Hi,

 

I'm trying to compare the data of two sets of 6 columns each (i.e. comparing column 1 of set 1 with column 1 of set 2 etc.). Both columns have one of three answers options in each cell. However, I'd be interest only in knowing whether two of these three different answer options between the first set of columns and the second set fit - I so far used an IF function such that I would get 0 if the answers of two cells are identical and 0 if there are not identical. Despite having deleted all answers that I do not want to code for as being 'different' between the two sets of columns, Excel still seems to compare them. Any idea how I could avoid this such that I will always receive a 0 (= identical) for this answer option between comparing the two sets of columns?

 

Example for what I tried but didn't work:

 

=IF(F2:F181=Z2:Z181;1;IF(F2:F181=same;1;0))

 

Here I tried to establish that if the answer of the column F2:F181 shows the answer option 'same', I would like the formula to give '1' back.

 

Thanks!

Formulas & Functions
tips and tricks
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Jul 15, 2018

    So you can just use an IF condition:

    IF(H1="same";"";if(H1=H2;"same";"different"))

  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jul 14, 2018

    You need to use formulas when you set cells. A1:A4=B1:B4 is not a formula and the machine does not understand what are you looking for.

     

    You said: "I so far used an IF function such that I would get 0 if the answers of two cells are identical and 0 if there are not identical."

    I assume you made an error in typing, if not, if you assign 0 if something is true and 0 if something is not true, it's ok that the result is always 0.

     

    Can you please attach here a sample file with a couple of rows of data, just to understand the mechanics you're looking for?

    I asked that because "(i.e. comparing column 1 of set 1 with column 1 of set 2 etc.)" it's not self explanatory and that "etc" does not give me idea of what you need.

     

    Thanks for the attention, bye!

    • Alexander Fries's avatar
      Alexander Fries
      Copper Contributor
      Jul 14, 2018

      Hi Arul,

       

      Good point!

       

      Here's two columns with example rows of data. Basically, I'm just trying to get a new column which contains the information whether the rows of the two presented columns match or not (i.e. if both say decreased in the same row). However, I would like to exclude 'same' such that when comparing a 'same' answer in a row of the first column with either an 'increased', 'decreased' or 'same' answer in the same row of the second column, they would never be treated as 'different' answers outputs in the formula. In other words, I would only like to compare whether 'increased' or 'decreased' in the first column rows change in their according second column row.

       

      Hope it's a bit clearer now and many thanks for your help!

       

      • Arul Tresoldi's avatar
        Arul Tresoldi
        Iron Contributor
        Jul 15, 2018

        So you can just use an IF condition:

        IF(H1="same";"";if(H1=H2;"same";"different"))