Formula or VB Code in a data sheet

[Vishaal]

Hi and Thanks in advance

i have post earlier some problem and get the solution but that was in complete and also i have lost that post url.

Problem is, 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....

 

[Joe4]

We have already told you that i have search and not find

All the posts are there.

So if you did not find it, then either:
1. You actually did not post it to this site, but another one.
2. You did not look through all the posts.
3. You were mistaken.

Also that solutions was incomplete.

That was not clear from your original post, as you said:

i have post earlier some problem and get the solution but that was in complete and also i have lost that post url.

"Complete" and "Incomplete" mean very different things (they actually mean the opposite of one another). So I thought you were saying it was complete.

if possible pls provide any formula or code,

I am sorry, but I don't think I can be of much assistance. Quite frankly, I have read through it a few times, and I really do not understand your question.

 

[Vishaal]

Hi Joe4, really you are great for giving me time and posting the comment,

believe me, i have no intention of wasting of any body time and you have other work also but pls help me to solve this through any formula or code.


now make it simple, can you please solve this if possible

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....

 

[Vishaal]

pls consider my request and provide code or formula if any

 

[Vishaal]

hi , find the post

thanks.

this is the code

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:
Sub MG28May04
Dim Rng As Range, Dn As Range, Num As String, c As Long, K As Variant
Set Rng = Range("B5", Range("B" & Rows.Count).End(xlUp))
Num = Join(Application.Transpose(Range("B2:B4")), ",")

With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

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

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

 

[Vishaal]

can we do it through formula