Forum Discussion

Jack67's avatar
Jack67
Copper Contributor
Jan 02, 2022

Excel help needed in comparing Data

 

Hi All

 
I need some help with excel formula. Here is my problem statement. Any help/pointers on how to solve this is really helpful
 
I have 2 documents - Doc1 and Doc2. Below is sample format. In this I want to match the data of an ID for a given month across Doc1 and Doc2 and display results.
 
For Eg - In Doc1 , ID '11' has January = 1 and in Doc2 also ID '11' has January = 1. Hence in Results I want 'Yes'
But In Doc1, ID '11' has April = 1 but in Doc2, ID '11' has April = 0. So then in Results I want 'No'

 

 

Doc 1

 

 

 

 

ID

January

February

March

April

11

1

1

1

1

22

1

1

0

0

 

 

 

 

 

 

 

 

 

 

Doc 2

 

 

 

 

ID

January

February

March

April

11

1

1

1

0

22

0.5

1

1

0

 

 

 

 

 

 

 

 

 

 

Results Match ?

 

 

 

 

ID

January

February

March

April

11

Yes

Yes

Yes

No

22

No

Yes

No

Yes

 

 

 

excel

14 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 02, 2022

    Jack67 

    As variant

    =IF(
   INDEX( [Doc1.xlsx]Sheet1!$C$3:$F$4,
           MATCH($B9,[Doc1.xlsx]Sheet1!$B$3:$B$4,0),
           MATCH(C$8,[Doc1.xlsx]Sheet1!$C$2:$F$2,0)) =
   INDEX( $C$3:$F$4,
            MATCH( $B9, $B$3:$B$4, 0),
            MATCH( C$8,  $C$2:$F$2, 0) ), "Yes", "No" )
    • Jack67's avatar
      Jack67
      Copper Contributor
      Jan 02, 2022
      Can you copy past the content from the attached xls in this reply here ? Also a correction - Instead of having 2 documents Doc1 and Doc2, I actually can put all the content in 1 sheet of a xls.

      So consider this as 1 single sheet of a xls with Part 1, Part 2 and then finally the Results
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 02, 2022

    Jack67 See if you can get the formulae as per the attached workbook into your own. I've used named ranges to avoid direct cell references and there are probably several other ways to do it. This, however, was the first that came to mind without giving it much further thoughts.

     

    By the way, the formulae actually result in TRUE or FALSE. With the double dash "--" in front, these become ones and zeroes. These are then custom formatted to display Yes or No.

Resources