Excel help needed in comparing Data

Copper Contributor

 

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

 

 

 

14 Replies

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

Hi @Jack67 

Assuming not necessary Excel 365 you can use VLOOKUP to lookup the ID in doc 1 and compare with doc 2.

If(same, "Yes", otherwise "No")

=IF(VLOOKUP($A15;$A$3:$E$4;COLUMNS($A:B);0)=VLOOKUP($A15;$A$9:$E$10;COLUMNS($A:B);0);"Yes";"No")

Also, attached.

@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" )
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

@Jack67 

What are exactly Part1 and Part2  - ranges in two separate sheets in same workbook, or ranges in same sheet, or structured tables?

@Jack67 Isn't that in the file I uploaded earlier?

@Sergei Baklan

 

Here is updated table. All this data is in 1 tab of a xls sheet

Section 1 has data and Section 2 has data

Results section should have the comparison results. I have added 2 more scenarios as well -  ID is in section 1 but missing in section 2 and other way around.

 

I have added sample row and column numbers as well. Can you please check and share updated formula that I can use in result section ?

 

2

B

C

D

E

F

G

3

 

section 1

 

 

 

 

4

 

ID

January

February

March

April

5

 

11

1

1

1

1

6

 

22

1

1

0

0

7

 

33

1

1

1

1

8

 

 

 

 

 

 

9

 

section 2

 

 

 

 

10

 

ID

January

February

March

April

11

 

11

1

1

1

0

12

 

22

0.5

1

1

0

13

 

44

1

1

1

1

14

 

 

 

 

 

 

15

 

Results Section

 

 

 

 

16

 

ID

January

February

March

April

17

 

11

Yes

Yes

Yes

No

18

 

22

No

Yes

No

Yes

19

 

33

Missing in section 2

Missing in section 2

Missing in section 2

Missing in section 2

20

 

44

Missing in section 1

Missing in section 1

Missing in section 1

Missing in section 1

 

I am facing issues in downloading files. Hence was requesting for details in the chat..

@Jack67 Like this then?

Screenshot 2022-01-02 at 12.19.43.png

@Jack67 

In C17 here

image.png

enter

=IF(
      ISNA( MATCH($B17, $B$5:$B$7, 0)),
      "Missing in Section 1",
IF( ISNA( MATCH( $B17, $B$11:$B$13, 0)),
      "Missing in Section 2",
IF( INDEX($C$5:$F$7,
          MATCH($B17, $B$5:$B$7, 0),
          MATCH(C$16, $C$4:$F$4, 0) ) =
     INDEX($C$11:$F$13,
           MATCH($B17, $B$11:$B$13, 0),
           MATCH(C$16, $C$4:$F$4, 0) ),
 "Yes", "No") ))

and drag it to the right and down

>@Ri Isn't that in the file I uploaded earlier?

@Riny_van_Eekelen Dito. And You also took height for allowing different months in the three ranges which I did not.

I like using formats to visualize the result as you did and at the same time keep the possibility to easily sum, eg to make a KPI showing equality (by dividing with count).

Preferred using N() (as it is a documented function and there is also a T() to get as Text) but it seems not upgraded to be dynamic array compatible and thus, it has to be the more secret --().

Thank you for sharing your response here. These pointers were very helpful for me.

Appreciate your time to support me here..
Thank you - This was super helpful to me.

Appreciate your pointers to resolve this question.

@Jack67 , you are welcome