# Excel help needed in comparing Data

Occasional Contributor

# 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

14 Replies

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

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.

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

@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

# Re: Excel help needed in comparing Data

@Jack67 Like this then?

# Re: Excel help needed in comparing Data

In C17 here

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

# Re: Excel help needed in comparing Data

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

# Re: Excel help needed in comparing Data

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

Appreciate your time to support me here..

# Re: Excel help needed in comparing Data

Thank you - This was super helpful to me.

Appreciate your pointers to resolve this question.

# Re: Excel help needed in comparing Data

@Jack67 , you are welcome