Difficult count matches with past data

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTU
1
2
3
4
5Num1234567891011121314Match Result With Row 7Match Result With Row 8Match Result With Row 9
6Explanation Part-2
7Row 7X11121212X11X1
8Row 8XX21XX1121X211
9Row 9X1X212XXX111XX
10Check these data with Row 7, 8 ,911X121XXX121X1714
11
12
13
14
15Explanation Part-1
16Row 7X11121212X11X1
17
18
19Row 1011X121XXX121X17
20
21
22
23Row 8XX21XX1121X211
24
25Row 1011X121XXX121X13
26
27
28
29
30Row 9X1X212XXX111XX
31Row 1011X121XXX121X18
32
Trail
Cell Formulas
RangeFormula
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
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
ABCDEFGHIJKLMNOPQRSTUV
1
2
3
4
5Num1234567891011121314Match with past data 5Match with past data 4Match with past data 3Match with past data 2Match with past data 1
6Explanation Part-2
7Row 7X11121212X11X1
8Row 8XX21XX1121X211
9Row 9X1X212XXX111XX
10Row 1011X121XXX121X1
11Row 11X11121212X11X1
12Row 12XX21XX1121X21195000
13Row 13X1X212XXX111XX212000
14Row 1411X121XXX121X186000
15Row 1521X11X1112111242310
16Row 161111121121XXX173210
17Row 171111121112111245131
Sheet3


Thank you

Kind Regards,
Kishan
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUV
1
2
3
4
5Num1234567891011121314Match with past data 5Match with past data 4Match with past data 3Match with past data 2Match with past data 1
6Explanation Part-2
7Row 7X11121212X11X1
8Row 8XX21XX1121X211
9Row 9X1X212XXX111XX
10Row 1011X121XXX121X1
11Row 11X11121212X11X1
12Row 12XX21XX1121X21159000
13Row 13X1X212XXX111XX212000
14Row 1411X121XXX121X186000
15Row 1521X11X1112111242310
16Row 161111121121XXX173210
17Row 171111121112111245131
Sheet3


Thank you

Kind Regards,
Kishan
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :)

 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top