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

Marcelo Branco · Sep 2, 2017

You are welcome. Glad to help

M.

Kishan · Sep 3, 2017

Hi,

I know it is hectic to add some more features when query is solved but I checked with my real data realized that I need to check with past 5 data instead 3.

I am sorry to bother you again here is my new request.

Row 12 check match with row 7, 8, 9, 10 & 11
Row 13 check match with row 8, 9, 10, 11 & 12
Row 14 check match with row 9, 10, 11, 12 & 13
Row 15 check match with row 10, 11, 12, 13 & 14
Row 16 check match with row 11, 12, 13, 14 & 15
Row 17 check match with row 12, 13, 14, 15 & 16
And so on...

Please need help

Sample data...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

2

3

4

5

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Match with past data 5

Match with past data 4

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

11

Row 11

X

1

2

1

2

1

2

X

1

X

1

12

Row 12

X

2

1

X

1

2

1

X

2

1

9

5

0

13

Row 13

X

1

X

2

1

2

X

1

X

2

12

0

14

Row 14

1

X

1

2

1

X

1

2

1

X

1

8

6

0

15

Row 15

2

1

X

1

X

1

2

1

2

4

2

3

1

0

16

Row 16

1

2

1

2

1

X

1

7

3

2

1

0

17

Row 17

1

2

1

2

1

2

4

5

1

3

1

Sheet3

Thank you

Kind Regards,
Kishan

Marcelo Branco · Sep 3, 2017

Are the results you showed correct? 9 comparing row 12 with row 7 (???)
Isn't necessary to have good eyes to notice that this is not correct.

Be careful when showing the expected results. Please, check every one before posting a question.

M.

Kishan · Sep 3, 2017

Marcelo Branco said:
Are the results you showed correct? 9 comparing row 12 with row 7 (???)
Isn't necessary to have good eyes to notice that this is not correct.

Be careful when showing the expected results. Please, check every one before posting a question.

M.

I am Sorry Marcelo Branco, and thank you for the watching it closely, it is my mistake I will try to check twice or thrice in the feature I do understand it is annoying for every one while he is working and results does not match. My apologise for the inconvenience

Please could you check below the correct results should be as shown.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

2

3

4

5

Num

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Match with past data 5

Match with past data 4

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

11

Row 11

X

1

2

1

2

1

2

X

1

X

1

12

Row 12

X

2

1

X

1

2

1

X

2

1

5

9

0

13

Row 13

X

1

X

2

1

2

X

1

X

2

12

0

14

Row 14

1

X

1

2

1

X

1

2

1

X

1

8

6

0

15

Row 15

2

1

X

1

X

1

2

1

2

4

2

3

1

0

16

Row 16

1

2

1

2

1

X

1

7

3

2

1

0

17

Row 17

1

2

1

2

1

2

4

5

1

3

1

Sheet3

Thank you

Kind Regards,
Kishan

Marcelo Branco · Sep 3, 2017

Try this

Code:

Sub aTest()
    Dim firstRow As Long, lastRow As Long
    Dim vDataAbove As Variant, vDataBase As Variant
    Dim arrResult(1 To 5) 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 - 5
        vDataAbove = Range("C" & i & ":P" & i + 4)
        vDataBase = Range("C" & i + 5 & ":P" & i + 5)
        For j = 1 To 5
            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 + 5 & ":V" & i + 5) = arrResult
    Next i
End Sub

M.

Marcelo Branco · Sep 3, 2017

A better solution: works with any number of rows above
All you have to do is to set the value of variable NumRows accordingly.

Code:

Sub Generic()
    Dim NumRows As Long
    Dim firstRow As Long, lastRow As Long
    Dim vDataAbove As Variant, vDataBase As Variant
    Dim arrResult() As Long, i As Long, j As Long, k As Long
    
    'Define Num rows above
    [COLOR=#ff0000]NumRows[/COLOR] = 5
    
    ReDim arrResult(1 To NumRows)
    firstRow = 7
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = firstRow To lastRow - NumRows
        vDataAbove = Range("C" & i & ":P" & i + NumRows - 1)
        vDataBase = Range("C" & i + NumRows & ":P" & i + NumRows)
        For j = 1 To NumRows
            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 + NumRows).Resize(, NumRows) = arrResult
    Next i
End Sub

M.

Kishan · Sep 3, 2017

Marcelo Branco said:

A better solution: works with any number of rows above
All you have to do is to set the value of variable NumRows accordingly.

Code:

Sub Generic()
    Dim NumRows As Long
    Dim firstRow As Long, lastRow As Long
    Dim vDataAbove As Variant, vDataBase As Variant
    Dim arrResult() As Long, i As Long, j As Long, k As Long
    
    'Define Num rows above
    [COLOR=#ff0000]NumRows[/COLOR] = 5
    
    ReDim arrResult(1 To NumRows)
    firstRow = 7
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = firstRow To lastRow - NumRows
        vDataAbove = Range("C" & i & ":P" & i + NumRows - 1)
        vDataBase = Range("C" & i + NumRows & ":P" & i + NumRows)
        For j = 1 To NumRows
            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 + NumRows).Resize(, NumRows) = arrResult
    Next i
End Sub

M.

Thank you Marcelo Branco, for reconciliation the request and fulfilling it as per my new request.

And making the code Generic this is just an Amazing idea!! It is working superb with any numbers of rows. Wow!!

I do appreciate a lot of your valuable time; you have spent to solve it twice.

Thank you very much for your support. You have saved my lot of time.

Good Luck

Have a good start of a new week ahead

Kind Regards,
Kishan

Marcelo Branco · Sep 3, 2017

You are welcome. Thanks for the feedback.

M.

Difficult count matches with past data

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Excel Facts

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

Difficult count matches with past data

Well-known Member

MrExcel MVP

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

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