Searching dates
 

To explain my question in a better way I have uploaded my worksheet on my
website http://www25.brinkster.com/shreejipc/Mihit/Book2.htm

What I want to do is:

Search all the five numbers 7 40 61 62 68 in the first row (1-Sep). If all
the 5 numbers are present in that row, put the date 1-Sep in cell AE1. If
not, go to the second row. Do this till the last row.

Do the same task for the next range 10 11 42 45 63

The answer is AE1 will have 2-Sep as all the five number were present in the
second row and AF1 will have 5-Sep again the same 5 numbers were present in
5th row.

AE2 will have 3-Sep as 10 11 42 45 63 were present in the 3rd row.

I have data will march end and doing this manully will be a very tedious
task. Can this be automated using a formula or VBA?

Maxi

Searching dates
 

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(1, "y"), _
Cells(1, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 31
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy





"mac_see" wrote in message
...
To explain my question in a better way I have uploaded my worksheet on my
website http://www25.brinkster.com/shreejipc/Mihit/Book2.htm

What I want to do is:

Search all the five numbers 7 40 61 62 68 in the first row (1-Sep). If all
the 5 numbers are present in that row, put the date 1-Sep in cell AE1. If
not, go to the second row. Do this till the last row.

Do the same task for the next range 10 11 42 45 63

The answer is AE1 will have 2-Sep as all the five number were present in
the
second row and AF1 will have 5-Sep again the same 5 numbers were present
in
5th row.

AE2 will have 3-Sep as 10 11 42 45 63 were present in the 3rd row.

I have data will march end and doing this manully will be a very tedious
task. Can this be automated using a formula or VBA?

Maxi

Searching dates
 

Works fine.

If I insert one row on top to put the headings, the vba does not work???

Also what changes I will have to make to start the result from column "AS"
instead of "AE"

Thanx
Maxi

"Tom Ogilvy" wrote:

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(1, "y"), _
Cells(1, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 31
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy

Searching dates
 

I am sorry, I want to start the result from column "AV" instead of "AE"


"mac_see" wrote:

Works fine.

If I insert one row on top to put the headings, the vba does not work???

Also what changes I will have to make to start the result from column "AS"
instead of "AE"

Thanx
Maxi

Searching dates
 

I don't mean to sound ungrateful for the opportunity to try to solve your
problem, but why bother to put up an example that doesn't match your
situation? One way to modify the code would be to sit down and figure out
how it works. Then make the necessary alterations. Nonetheless, this
should work for labels in Row 1 and results starting in AS2

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(2, 3), _
Cells(2, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(2, "y"), _
Cells(2, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"mac_see" wrote in message
...
Works fine.

If I insert one row on top to put the headings, the vba does not work???

Also what changes I will have to make to start the result from column "AS"
instead of "AE"

Thanx
Maxi

"Tom Ogilvy" wrote:

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(1, "y"), _
Cells(1, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 31
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy

Searching dates
 

Thank you for your help.

I realized it later that I forgot to add a row for the headings and I know
little about VBA but your comment in the code helped me to understand how it
works.

Thanx again for your efforts

Maxi

"Tom Ogilvy" wrote:

I don't mean to sound ungrateful for the opportunity to try to solve your
problem, but why bother to put up an example that doesn't match your
situation? One way to modify the code would be to sit down and figure out
how it works. Then make the necessary alterations. Nonetheless, this
should work for labels in Row 1 and results starting in AS2

Searching dates
 

I have another question. The code searches all the five numbers 7 40 61 62 68
in the first row (1-Sep) till the last row. If I want to search any 4 numbers
out of these five ([7, 40, 61, 62] or [7, 40, 61, 68] or [7, 40, 62, 68] or
[7, 61, 62, 68] or [40, 61, 62, 68]), what alterations will I have to do?

I tried to modify the code but I am not sure what I am doing wrong. I
understood half of the code but did not understand why you kept "cnt" and the
line "res = Application.Match(v(1, k), v1, 0)"

Please help

Maxi

"Tom Ogilvy" wrote:

I don't mean to sound ungrateful for the opportunity to try to solve your
problem, but why bother to put up an example that doesn't match your
situation? One way to modify the code would be to sit down and figure out
how it works. Then make the necessary alterations. Nonetheless, this
should work for labels in Row 1 and results starting in AS2

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(2, 3), _
Cells(2, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(2, "y"), _
Cells(2, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"mac_see" wrote in message
...
Works fine.

If I insert one row on top to put the headings, the vba does not work???

Also what changes I will have to make to start the result from column "AS"
instead of "AE"

Thanx
Maxi

"Tom Ogilvy" wrote:

Option Explicit
Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Set tbl = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(1, "y"), _
Cells(1, "y").End(xlDown)).Resize(, 5)
For i = 1 To tbl1(tbl1.Count).Row
icol = 31
v = Cells(i, 25).Resize(1, 5).Value
For j = 1 To tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt < k Then Exit For
Next k
If cnt = 5 Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy

Searching dates
 

I know that either I have to do something to this line "res =
Application.Match(v(1, k), v1, 0)" or use countif worksheet function but I am
not sure how to use it.

Tom, I tried my best to manipulate this code continuously for 4 days with no
luck. Can you please help?

Maxi

"mac_see" wrote:

I have another question. The code searches all the five numbers 7 40 61 62 68
in the first row (1-Sep) till the last row. If I want to search any 4 numbers
out of these five ([7, 40, 61, 62] or [7, 40, 61, 68] or [7, 40, 62, 68] or
[7, 61, 62, 68] or [40, 61, 62, 68]), what alterations will I have to do?

I tried to modify the code but I am not sure what I am doing wrong. I
understood half of the code but did not understand why you kept "cnt" and the
line "res = Application.Match(v(1, k), v1, 0)"

Please help

Maxi

Searching dates
 

Set NumMatch to the number of matches

Right now it would do 4 or greater with NumMatch = 4

if you want exactly 4 (exclude 5 matches) then change

If cnt = NumMatch Then

to
If cnt = NumMatch Then

But I would assume you want 4 or greater.

Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Dim NumMatch As Long, diff As Long
'
NumMatch = 4
'
diff = 5 - NumMatch
Set tbl = Range(Cells(2, 3), _
Cells(2, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(2, "y"), _
Cells(2, "y").End(xlDown)).Resize(, 5)
For i = 2 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is 45
v = Cells(i, 25).Resize(1, 5).Value
For j = 2 To tbl(tbl.Count).Row
Debug.Print i, j, tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt = NumMatch Or cnt < k - diff Then Exit For
Next k
If cnt = NumMatch Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"mac_see" wrote in message
...
I know that either I have to do something to this line "res =
Application.Match(v(1, k), v1, 0)" or use countif worksheet function but I
am
not sure how to use it.

Tom, I tried my best to manipulate this code continuously for 4 days with
no
luck. Can you please help?

Maxi

"mac_see" wrote:

I have another question. The code searches all the five numbers 7 40 61
62 68
in the first row (1-Sep) till the last row. If I want to search any 4
numbers
out of these five ([7, 40, 61, 62] or [7, 40, 61, 68] or [7, 40, 62, 68]
or
[7, 61, 62, 68] or [40, 61, 62, 68]), what alterations will I have to
do?

I tried to modify the code but I am not sure what I am doing wrong. I
understood half of the code but did not understand why you kept "cnt"
and the
line "res = Application.Match(v(1, k), v1, 0)"

Please help

Maxi

Searching dates
 

Nice logic....... and I was thinking of using countif instead of match

Thanx a million

Maxi

"Tom Ogilvy" wrote:

Set NumMatch to the number of matches

Right now it would do 4 or greater with NumMatch = 4

if you want exactly 4 (exclude 5 matches) then change

If cnt = NumMatch Then

to
If cnt = NumMatch Then

But I would assume you want 4 or greater.

Sub AA()
Dim icol As Long, i As Long, j As Long
Dim k As Long, v As Variant, v1 As Variant
Dim tbl As Range, tbl1 As Range
Dim res As Variant, cnt As Long
Dim NumMatch As Long, diff As Long
'
NumMatch = 4
'
diff = 5 - NumMatch
Set tbl = Range(Cells(2, 3), _
Cells(2, 3).End(xlDown)).Resize(, 21)
Set tbl1 = Range(Cells(2, "y"), _
Cells(2, "y").End(xlDown)).Resize(, 5)
For i = 2 To tbl1(tbl1.Count).Row
icol = 45 ' AE is 31, AS is 45
v = Cells(i, 25).Resize(1, 5).Value
For j = 2 To tbl(tbl.Count).Row
Debug.Print i, j, tbl(tbl.Count).Row
v1 = Cells(j, 3).Resize(1, 21)
cnt = 0
For k = 1 To 5
res = Application.Match( _
v(1, k), v1, 0)
If Not IsError(res) Then
cnt = cnt + 1
End If
If cnt = NumMatch Or cnt < k - diff Then Exit For
Next k
If cnt = NumMatch Then
Cells(i, icol) = Cells(j, 2).Value
icol = icol + 1
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"mac_see" wrote in message
...
I know that either I have to do something to this line "res =
Application.Match(v(1, k), v1, 0)" or use countif worksheet function but I
am
not sure how to use it.

Tom, I tried my best to manipulate this code continuously for 4 days with
no
luck. Can you please help?

Maxi

"mac_see" wrote:

I have another question. The code searches all the five numbers 7 40 61
62 68
in the first row (1-Sep) till the last row. If I want to search any 4
numbers
out of these five ([7, 40, 61, 62] or [7, 40, 61, 68] or [7, 40, 62, 68]
or
[7, 61, 62, 68] or [40, 61, 62, 68]), what alterations will I have to
do?

I tried to modify the code but I am not sure what I am doing wrong. I
understood half of the code but did not understand why you kept "cnt"
and the
line "res = Application.Match(v(1, k), v1, 0)"

Please help

Maxi