Jan 02 2022 12:15 AM
Hi All
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 |
Jan 02 2022 12:44 AM
@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.
Jan 02 2022 12:47 AM
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.
Jan 02 2022 12:54 AM
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" )
Jan 02 2022 01:05 AM
Jan 02 2022 01:44 AM
What are exactly Part1 and Part2 - ranges in two separate sheets in same workbook, or ranges in same sheet, or structured tables?
Jan 02 2022 01:49 AM
@Jack67 Isn't that in the file I uploaded earlier?
Jan 02 2022 03:05 AM
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 |
Jan 02 2022 03:06 AM
Jan 02 2022 03:33 AM
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
Jan 02 2022 03:59 AM
>@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 --().
Jan 02 2022 05:51 AM
Jan 02 2022 05:52 AM