Forum Discussion
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" )
So consider this as 1 single sheet of a xls with Part 1, Part 2 and then finally the Results
Jack67 Isn't that in the file I uploaded earlier?
>@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 --().
- I am facing issues in downloading files. Hence was requesting for details in the chat..
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
What are exactly Part1 and Part2 - ranges in two separate sheets in same workbook, or ranges in same sheet, or structured tables?
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
