Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching between dates | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
Searching on Dates | Excel Worksheet Functions | |||
Searching For Files with Dates | Excel Programming | |||
searching for a range of dates | Excel Programming |