Difficult count matches with past data

Kishan · Sep 1, 2017

Using Excel 2000

Hi,

It is bit hard I will try to explain best possible

Explanation Part-1
Normally counting the match of the row10 individually with row 7, 8, 9 we will get the result as shown in the area B16:T31 using the given formula
(Row10 with Row7) Match in cell R19=7
(Row10 with Row8) Match in cell S25=3
(Row10 with Row9) Match in cell T31=8

Explanation Part-2, which I require the results
Really I need the match result of row10 with row7, row8 & row9
As shown cells R10=7, S10=1 & in T10=4 why these results?
Row10 match result with row7 can be calculated by given formula which is correct = 7

But here is the twist when row10 (check match with row8 it must take in the account) that out of 3 match 2 has been counted already by row10 with row7 so the result will be 1 not 3 which you can check and see in cells C23:P25 Explanation Part-1

Now row10 match result with row9 (check match with row7 & row8 must be taken in the account) that out of 8 match 4 has been counted already by row10 with row7 & row8 so the result will be 4 not 8 which you can check and see in cells C30:P31 Explanation Part-1

Example data...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

2

3

4

5

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Match Result With Row 7

Match Result With Row 8

Match Result With Row 9

6

Explanation Part-2

7

Row 7

X

1

2

1

2

1

2

X

1

X

1

8

Row 8

X

2

1

X

1

2

1

X

2

1

9

Row 9

X

1

X

2

1

2

X

1

X

10

Check these data with Row 7, 8 ,9

1

X

1

2

1

X

1

2

1

X

1

7

1

4

11

12

13

14

15

Explanation Part-1

16

Row 7

X

1

2

1

2

1

2

X

1

X

1

17

18

19

Row 10

1

X

1

2

1

X

1

2

1

X

1

7

20

21

22

23

Row 8

X

2

1

X

1

2

1

X

2

1

24

25

Row 10

1

X

1

2

1

X

1

2

1

X

1

3

26

27

28

29

30

Row 9

X

1

X

2

1

2

X

1

X

31

Row 10

1

X

1

2

1

X

1

2

1

X

1

8

32

Trail

Cell Formulas
Range	Formula
R19	=SUMPRODUCT(--(C19:P19=C16:P16))
S25	=SUMPRODUCT(--(C25:P25=C23:P23))
T31	=SUMPRODUCT(--(C31:P31=C30:P30))

Hope I explained it

Thank you in advance

Kishan

Nishant94 · Sep 2, 2017

Unknown

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Row 7

Row 8

Row 9

3

Explanation Part-2

4

Row 7

X

1

2

1

2

1

2

X

1

X

1

5

Row 8

X

2

1

X

1

2

1

X

2

1

6

Row 9

X

1

X

2

1

2

X

1

X

7

Check these data with Row 7, 8 ,9

1

X

1

2

1

X

1

2

1

X

1

7

1

4

8

Sheet15

In Q7 enter this formula with Ctrl+Shift+Enter:

Code:

=SUM(--($B$7:$O$7=INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0)))-                                
                       SUM(--(IF(COLUMNS($Q2:Q2)>1,IF($B$7:$O$7=
                                INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0),
                                         $B$4:INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0)-1,0))=
                                                INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0),0)))

Kishan · Sep 2, 2017

Nishant94 said:
Unknown

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]Num
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]6
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]8
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]9
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]10
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]11
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]12
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]13
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]14
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FFFF]#00FFFF[/URL] , align: center"]Row 7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]Row 8
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]Row 9
[/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Explanation Part-2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Row 7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Row 8
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Row 9
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]

[TD="align: center"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Check these data with Row 7, 8 ,9
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]X
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]4
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet15

In Q7 enter this formula with Ctrl+Shift+Enter:

Code:

=SUM(--($B$7:$O$7=INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0)))- SUM(--(IF(COLUMNS($Q2:Q2)>1,IF($B$7:$O$7= INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0), $B$4:INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0)-1,0))= INDEX($B$4:$O$6,MATCH(Q$2,$A$4:$A$6,0),0),0)))

Hi Nishant Ghosh, I am surprise how you have written a so long formula which is giving a expected results correct. Thank you so much

I appreciate your help

Have a good day

Regards,
Kishan

Kishan · Sep 2, 2017

Hi,

Formula Post#2 is working with my Post#1 and giving the results correct. I tried to modified the formula to make it work with the new layout shown below but could not succeed. Which could check match with multiply row using the method as explained in Post#1
Row 10 check match with row 7, 8 & 9
Row 11 check match with row 8, 9 & 10
Row 12 check match with row 9, 10 & 11
Row 13 check match with row 10, 11 & 12
Row 14 check match with row 11, 12 & 13
Row 15 check match with row 12, 13 & 14
And so on............

Please I need VBA if could possible to get solve this complex match

I do appreciate your help

Example data...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

3

4

5

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Match with past data 3

Match with past data 2

Match with past data 1

6

Explanation Part-2

7

Row 7

X

1

2

1

2

1

2

X

1

X

1

8

Row 8

X

2

1

X

1

2

1

X

2

1

9

Row 9

X

1

X

2

1

2

X

1

X

10

Row 10

1

X

1

2

1

X

1

2

1

X

1

7

1

4

11

Row 11

X

1

2

1

2

1

2

X

1

X

1

5

4

3

12

Row 12

X

2

1

X

1

2

1

X

2

1

2

13

Row 13

X

1

X

2

1

2

X

1

X

8

2

0

14

Row 14

1

X

1

2

1

X

1

2

1

X

1

7

1

4

15

Row 15

2

1

X

1

X

1

2

1

2

5

0

16

Row 16

1

2

1

2

1

X

1

5

3

2

17

Row 17

1

2

1

2

1

2

8

4

2

18

19

Trail-1

Hope I explain it

Regards,
Kishan

Kishan · Sep 2, 2017

Kishan · Sep 2, 2017

Hi,

If the VBA could not a solution
Please help with formula it will be ok

Thank you

Regards,
Kishan

Marcelo Branco · Sep 2, 2017

I don't understand some results
T11= 3
i think 2 is the correct result

R17:T17 8 4 2
I think the correct is 4 8 2

Could you clarify?

M.

Kishan · Sep 2, 2017

Marcelo Branco said:
I don't understand some results
T11= 3
i think 2 is the correct result

R17:T17 8 4 2
I think the correct is 4 8 2

Could you clarify?

M.

Hi Marcelo Branco, nice catch really you have good eye Q
Your data are correct I have rectify a layout below as shown

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

3

4

5

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Match with past data 3

Match with past data 2

Match with past data 1

6

Explanation Part-2

7

Row 7

X

1

2

1

2

1

2

X

1

X

1

8

Row 8

X

2

1

X

1

2

1

X

2

1

9

Row 9

X

1

X

2

1

2

X

1

X

10

Row 10

1

X

1

2

1

X

1

2

1

X

1

7

1

4

11

Row 11

X

1

2

1

2

1

2

X

1

X

1

5

4

2

12

Row 12

X

2

1

X

1

2

1

X

2

1

2

13

Row 13

X

1

X

2

1

2

X

1

X

8

2

0

14

Row 14

1

X

1

2

1

X

1

2

1

X

1

7

1

4

15

Row 15

2

1

X

1

X

1

2

1

2

5

0

16

Row 16

1

2

1

2

1

X

1

5

3

2

17

Row 17

1

2

1

2

1

2

4

8

2

18

19

Trail-1

Thank you for the remark

Regards,
Kishan

Marcelo Branco · Sep 2, 2017

My eyes are not that good.

The macro that I created, and that seemed correct, that showed results different from yours,
There it is...

Code:

Sub aTest()
    Dim firstRow As Long, lastRow As Long
    Dim vDataAbove As Variant, vDataBase As Variant
    Dim arrResult(1 To 3) As Long, i As Long, j As Long, k As Long
    
    firstRow = 7
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = firstRow To lastRow - 3
        vDataAbove = Range("C" & i & ":P" & i + 2)
        vDataBase = Range("C" & i + 3 & ":P" & i + 3)
        For j = 1 To 3
            arrResult(j) = 0
            For k = LBound(vDataAbove, 2) To UBound(vDataAbove, 2)
                If vDataBase(1, k) = vDataAbove(j, k) Then
                    arrResult(j) = arrResult(j) + 1
                    vDataBase(1, k) = "N/A"
                End If
            Next k
        Next j
        Range("R" & i + 3 & ":T" & i + 3) = arrResult
    Next i
End Sub

Hope this helps

M.

Kishan · Sep 2, 2017

Marcelo Branco said:

My eyes are not that good.

The macro that I created, and that seemed correct, that showed results different from yours,
There it is...

Code:

Sub aTest()
    Dim firstRow As Long, lastRow As Long
    Dim vDataAbove As Variant, vDataBase As Variant
    Dim arrResult(1 To 3) As Long, i As Long, j As Long, k As Long
    
    firstRow = 7
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = firstRow To lastRow - 3
        vDataAbove = Range("C" & i & ":P" & i + 2)
        vDataBase = Range("C" & i + 3 & ":P" & i + 3)
        For j = 1 To 3
            arrResult(j) = 0
            For k = LBound(vDataAbove, 2) To UBound(vDataAbove, 2)
                If vDataBase(1, k) = vDataAbove(j, k) Then
                    arrResult(j) = arrResult(j) + 1
                    vDataBase(1, k) = "N/A"
                End If
            Next k
        Next j
        Range("R" & i + 3 & ":T" & i + 3) = arrResult
    Next i
End Sub

Hope this helps

M.

Wow!! Marcelo Branco, macro is just amazing it is giving a very correct result as per posrt#8. This were difficult task but you nailed it.

I do appreciate your help.

Thank you very much for giving a wonderful solution

Kind Regards,
Kishan

Difficult count matches with past data

Kishan

Well-known Member

Excel Facts

Nishant94

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Difficult count matches with past data

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock