VB Code or Macro - help me pls

[Vishaal]

Hi, pls check the below table and provide macro/vb code

we have a data in excel and want to check that how many times a certain task will repeat, you can understand it by checking the below excel

Excel 2007 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	S.No.​	Hours​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	1​	2​	We need to check the numbers 2,5,6 (or row number 1,2,3) from serial no. 4 in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	2​	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	3​	6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	4​	8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	5​	9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	6​	8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	7​	4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	8​	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	9​	4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​	10​	5​	We have find the number five here, and its come after 7 rows, Now again we need to check the numbers 5,4,1 (S.No. 8,9,10) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​	11​	2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​	12​	8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​	13​	7​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​	14​	1​	We have find the number one here, and its come after 4 rows, Now again we need to check the numbers 1,7,8 (S.No. 12,13,14) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	15​	6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​	16​	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​	17​	6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​	18​	7​	We have find the number Seven here, and its come after 4 rows, Now again we need to check the numbers 7,6,5 (S.No. 16,17,18) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​	19​	9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​	20​	9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​	21​	6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​	22​	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​	23​	2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​	24​	5​	We have find the number Five here, and its come after 6 rows,

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Result
1. we have found that after 7 row we find the value one time
2. we have found that after 6 row we find the value one time
3. we have found that after 4 row we find the value two time

so the result is

After 7 = 1
After 6 = 1
After 4 = 2

pls help and provide the code/macro​

 

[MickG]

Try this:-
Answer starts "E1", it is not the same as Yours !!!
NB:- Why when your looking for "5,6,7" , do you find "5" instead of "6" which comes after "6", and give it "6" rows when it seems to be "3" ????

[COLOR="Navy"]Sub[/COLOR] MG28May04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("B5", Range("B" & Rows.Count).End(xlUp))
Num = Join(Application.Transpose(Range("B2:B4")), ",")

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    [COLOR="Navy"]If[/COLOR] InStr(Num, Dn.Value) > 0 [COLOR="Navy"]Then[/COLOR]
        Num = Join(Application.Transpose(Dn.Offset(-2).Resize(3)), ",")
        .Item(c) = .Item(c) + 1
        c = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
c = 0

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, "E") = "After " & K & " = " & .Item(K)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Vishaal]

Many Many Thanks MickG for the code.......

checking the code and asked you if we need any editing in this code

 

[Vishaal]

Hi MickG,

checked, can it will show the result like this

i have following sheet

Col A	Col B
S.No.	Student Work
1	2
2	3
3	6
4	8
5	4
6	2
7	3
8	6

<tbody>
</tbody>

In the above sheet i have approx 140 row and in coloumn B, we have approx 70 values

and i want the three different result from this sheet

1 - We will check the value 2, 3 & 6 (S.No. 1, 2 & 3) from the S.No. 4 and when we find the any value we will update it as the given sheet,
After it will check the rest 2 value and we will update it as the given sheet
After getting 2 value, we will start the check last value and update it when we find it according to the given sheet

Col A	Col B	First		Second		Third
S.No.	Student Work	Result	After how many	Result	After how many	Result	After how many times
1	2
2	3
3	6
4	8
5	4
6	2	Yes	3
7	3			Yes	4
8	6					Yes	5

<tbody>
</tbody>

2 - We will check the value 2, 3 & 6 (S.No. 1, 2 & 3) from the S.No. 4 and when we find the any value we will update it as the given sheet,
After that we will again take the value 2,4,8 (S.No. 4, 5 & 6) from the S.No. 7 and when we find the any value we will update it as the given sheet,
After that we will again take the value 8,6,3 (S.No. 7, 8 & 9) from the S.No. 10 and when we find the any value we will update it as the given sheet,

Col A	Col B
S.No.	Student Work	Result	After How many times
1	2
2	3
3	6
4	8
5	4
6	2	Yes	3
7	3
8	6
9	8	Yes	3
10	4
11	5
12	0
13	0
14	7
15	3	Yes	6

<tbody>
</tbody>

Hope will get a solution....

 

[MickG]

Try this:-
Code for Data 1 results :-

[COLOR="Navy"]Sub[/COLOR] MG09Aug04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("B2:B4"): .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            col = col + 2
            Dn.Offset(, col - 1) = "Yes"
            Dn.Offset(, col) = Dn.Row - 4
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Code for Data 2 results:-

[COLOR="Navy"]Sub[/COLOR] MG09Aug48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]Set[/COLOR] nRng = Range("B2:B4")
[COLOR="Navy"]For[/COLOR] Rw = 0 To 2
   [COLOR="Navy"]Set[/COLOR] Rng = Range(nRng(nRng.Count + 1), Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng: .Item(R.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                    Dn.Offset(, 1) = "Yes"
                    Dn.Offset(, 2) = Dn.Row - nRng(nRng.Count).Row
                [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
.RemoveAll
[COLOR="Navy"]Set[/COLOR] nRng = nRng.Offset(3)
[COLOR="Navy"]Next[/COLOR] Rw

[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Vishaal]

love you MickG, great

again, you all guys are master in excel. really