Vishaal
Well-known Member
- Joined
- Mar 16, 2019
- Messages
- 533
- Office Version
- 2010
- 2007
- Platform
- Windows
- Web
Hi,
Thanks in advance
I am using the following vba code,
Code:
Sub MG09Aug48
Dim Rng As Range, Dn As Range, Rw As Long, col As Long, nRng As Range, R As Range
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Set nRng = Range("B2:B4")
For Rw = 0 To 2
Set Rng = Range(nRng(nRng.Count + 1), Range("B" & Rows.Count).End(xlUp))
For Each R In nRng: .Item(R.Value) = Empty: Next
For Each Dn In Rng
If .exists(Dn.Value) Then
Dn.Offset(, 1) = "Yes"
Dn.Offset(, 2) = Dn.Row - nRng(nRng.Count).Row
Exit For
End If
Next Dn
.RemoveAll
Set nRng = nRng.Offset(3)
Next Rw
End With
End Sub
In the following sheet
Excel 2010 32 bit
and i want the following result, its also working for me but its cant stop and restart for previous values, please check the sheet
Excel 2010 32 bit
Help Pls
Thanks in advance
I am using the following vba code,
Code:
Sub MG09Aug48
Dim Rng As Range, Dn As Range, Rw As Long, col As Long, nRng As Range, R As Range
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Set nRng = Range("B2:B4")
For Rw = 0 To 2
Set Rng = Range(nRng(nRng.Count + 1), Range("B" & Rows.Count).End(xlUp))
For Each R In nRng: .Item(R.Value) = Empty: Next
For Each Dn In Rng
If .exists(Dn.Value) Then
Dn.Offset(, 1) = "Yes"
Dn.Offset(, 2) = Dn.Row - nRng(nRng.Count).Row
Exit For
End If
Next Dn
.RemoveAll
Set nRng = nRng.Offset(3)
Next Rw
End With
End Sub
In the following sheet
Excel 2010 32 bit
A | B | C | |
---|---|---|---|
1 | |||
2 | 10089 | ||
3 | 3363 | ||
4 | 13452 | ||
5 | 3009 | ||
6 | 214 | ||
7 | 12744 | ||
8 | 56000 | ||
9 | 3363 | ||
10 | 66080 | ||
11 | 10620 | ||
12 | 10620 | ||
13 | 214 | ||
14 | 12744 | ||
15 | 17700 | ||
16 | 10620 | ||
17 | 5310 | ||
18 | 7080 | ||
19 | 7080 | ||
20 | 5310 | ||
21 | 10620 | ||
22 | 5060 | ||
23 | 13452 | ||
24 | 13452 | ||
25 |
Sheet: Sheet1 |
and i want the following result, its also working for me but its cant stop and restart for previous values, please check the sheet
Excel 2010 32 bit
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | Amount Given | Work | Amount Recd. | How Many Days | | Stop & Start | | |
2 | 10089 | check these values on upcoming cells and if the values are duplicate in rows then add previous another value | | | | | ||
3 | 3363 | | | | | |||
4 | 13452 | | 1 | | | |||
5 | 3009 | | | 2 | | | ||
6 | 214 | | | 3 | | | ||
7 | 12744 | check these values on upcoming cells and if the values are duplicate in rows then add previous another value | | 4 | | | ||
8 | 56000 | | 5 | Recd. After 5 days | ||||
9 | 3363 | Recd. | 1 | Stop the searching value of row 1,2,3 | Start the searching of Row value 7,8,9 | |||
10 | 66080 | | | 2 | | | ||
11 | 10620 | check these values on upcoming cells and if the values are duplicate in rows then add previous another value | | 3 | | | ||
12 | 10620 | | 4 | Recd. After 4 days | ||||
13 | 214 | Recd. | 1 | Stop the searching value of row 7,8,9 | Start the searching of Row value 10,11,12,13 because the value of row 11 & row 12 are same | |||
14 | 12744 | | | 2 | | | ||
15 | 17700 | | | 3 | | | ||
16 | 10620 | | | 4 | | | ||
17 | 5310 | | | 5 | | | ||
18 | 7080 | | | 6 | | | ||
19 | 7080 | | | 7 | | | ||
20 | 5310 | check these values on upcoming cells and if the values are duplicate in rows then add previous another value | | 8 | | | ||
21 | 10620 | | 9 | Recd. After 9 days | | | ||
22 | 5060 | Recd. | | Stop the searching value of row 10,11,12,13 | Start the searching of Row value 20,21,22 | |||
23 | 13452 | | | | | | ||
24 | 13452 | | | | | | ||
25 | | | | | | |
Sheet: Sheet1 |
Help Pls