VB Code or Macro - help me pls

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]
S.No.​
[/TD]
[TD]
Hours​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]
1​
[/TD]
[TD="bgcolor: #FFFF00"]
2​
[/TD]
[TD]We need to check the numbers 2,5,6 (or row number 1,2,3) from serial no. 4 in next coming rows[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]
3​
[/TD]
[TD="bgcolor: #FFFF00"]
6​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]
4​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]
6​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]
7​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]
8​
[/TD]
[TD="bgcolor: #FFFF00"]
1​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]
9​
[/TD]
[TD="bgcolor: #FFFF00"]
4​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]
10​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD]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[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[/TD]
[TD]
11​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[/TD]
[TD]
12​
[/TD]
[TD="bgcolor: #FFFF00"]
8​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[/TD]
[TD]
13​
[/TD]
[TD="bgcolor: #FFFF00"]
7​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[/TD]
[TD]
14​
[/TD]
[TD="bgcolor: #FFFF00"]
1​
[/TD]
[TD]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[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
[/TD]
[TD]
15​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[/TD]
[TD]
16​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
[/TD]
[TD]
17​
[/TD]
[TD="bgcolor: #FFFF00"]
6​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[/TD]
[TD]
18​
[/TD]
[TD="bgcolor: #FFFF00"]
7​
[/TD]
[TD]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[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
[/TD]
[TD]
19​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
[/TD]
[TD]
20​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
[/TD]
[TD]
21​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
[/TD]
[TD]
22​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
[/TD]
[TD]
23​
[/TD]
[TD="bgcolor: #FFFF00"]
2​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
[/TD]
[TD]
24​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD]We have find the number Five here, and its come after 6 rows,[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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​

 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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" ????
Code: 
[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
 
Last edited:
Upvote 0
Many Many Thanks MickG for the code.......

checking the code and asked you if we need any editing in this code
 
Upvote 0
Hi MickG,

checked, can it will show the result like this

i have following sheet

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Col A[/TH]
[TH="align: center"]Col B[/TH]
[/TR]
[TR]
[TD]S.No.[/TD]
[TD]Student Work[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Col A[/TH]
[TH="align: center"]Col B[/TH]
[TH="align: center"]First[/TH]
[TH="align: center"][/TH]
[TH="align: center"]Second[/TH]
[TH="align: center"][/TH]
[TH="align: center"]Third[/TH]
[TH="align: center"][/TH]
[/TR]
[TR]
[TD]S.No.[/TD]
[TD]Student Work[/TD]
[TD]Result[/TD]
[TD]After how many[/TD]
[TD]Result[/TD]
[TD]After how many[/TD]
[TD]Result[/TD]
[TD]After how many times[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


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,

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Col A[/TH]
[TH="align: center"]Col B[/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[/TR]
[TR]
[TD]S.No.[/TD]
[TD]Student Work[/TD]
[TD]Result[/TD]
[TD]After How many times[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD]Yes[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[TD]Yes[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


Hope will get a solution....
 
Upvote 0
Try this:-
Code for Data 1 results :-
Code: 
[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:-

Code: 
[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
 
Upvote 0
love you MickG, great

again, you all guys are master in excel. really
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
135
Fluff
Fluff
C
  • Solved
Extract data from pivot table into required format
Replies
2
Views
276
alansidman
A
JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
142
felixstraube
F
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
595
felixstraube
F
Yecart77
  • Question Question
Formula working on first row but not subsequent rows
Replies
1
Views
357
awoohaw
awoohaw

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

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