Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Advanced Filter - latest lines for multi criteria
Hi,
i have data like following, which is the salesman responsible for each state until a specific time: David - CA - 2008/1/12 Peter - NY - 2008/5/25 Susan - CA - 2009/3/25 David - NY - 2008/4/20 How do I search "CA" and it will show latest records for each salesman has been working in CA, ie, David - CA - 2008/1/12 Susan - CA - 2009/3/25 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Advanced Filter - latest lines for multi criteria
Look at this example:
http://en.allexperts.com/q/Excel-105...-FIND-COPY.htm Hopefully you learn a thing or two by doing this yourself...I'll give you the modified code just in case you are new to VBA... Sub CopyCA() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long Dim dRow As Long Dim sCount As Long sCount = 0 dRow = 0 For sRow = 1 To Range("A65536").End(xlUp).Row If Cells(sRow, "A") Like "*CA*" Then sCount = sCount + 1 dRow = dRow + 1 Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") End If Next sRow MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done" End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Angus" wrote: Hi, i have data like following, which is the salesman responsible for each state until a specific time: David - CA - 2008/1/12 Peter - NY - 2008/5/25 Susan - CA - 2009/3/25 David - NY - 2008/4/20 How do I search "CA" and it will show latest records for each salesman has been working in CA, ie, David - CA - 2008/1/12 Susan - CA - 2009/3/25 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Advanced Filter - latest lines for multi criteria
Ya know what, try this, and then you can search for anything:
Sub CopyCA() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long Dim dRow As Long Dim sCount As Long sCount = 0 dRow = 0 myword = InputBox("Enter items to search for.") For sRow = 1 To Range("A65536").End(xlUp).Row If Cells(sRow, "A") Like "*" & myword & "*" Then sCount = sCount + 1 dRow = dRow + 1 Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") End If Next sRow MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done" End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Look at this example: http://en.allexperts.com/q/Excel-105...-FIND-COPY.htm Hopefully you learn a thing or two by doing this yourself...I'll give you the modified code just in case you are new to VBA... Sub CopyCA() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long Dim dRow As Long Dim sCount As Long sCount = 0 dRow = 0 For sRow = 1 To Range("A65536").End(xlUp).Row If Cells(sRow, "A") Like "*CA*" Then sCount = sCount + 1 dRow = dRow + 1 Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") End If Next sRow MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done" End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Angus" wrote: Hi, i have data like following, which is the salesman responsible for each state until a specific time: David - CA - 2008/1/12 Peter - NY - 2008/5/25 Susan - CA - 2009/3/25 David - NY - 2008/4/20 How do I search "CA" and it will show latest records for each salesman has been working in CA, ie, David - CA - 2008/1/12 Susan - CA - 2009/3/25 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Advanced Filter - latest lines for multi criteria
Got it, thanks.
"Patrick Molloy" wrote: use an advanced filter on the table, where the critierion is State = CA then extract distinct Salesman to the cell on the right put this array formula: { =MAX((E9:E110=M9)*(F9:F110=K9)*(H9:H110)) } column E9:E110 is my list of salesman, F is the column of states and H the column of Dates "Angus" wrote in message ... Sorry, I amend my example again. Following is my data: David - CA - Customer A - 2008/4/20 David - CA - Customer B - 2009/3/31 Susan - CA - Customer C - 2008/7/20 If I search CA it should come with: David - CA - Customer B - 2009/3/31 Susan - CA - Customer C - 2008/7/20 As there is more than one records for David, I need only the latest one; and all other salesmen/ saleswomen worked in CA. Thanks. Angus "Angus" wrote: Hi Ryan, Thanks for your reply. It was my mistake that I didn't make it clear. For example, David - CA - Customer A - 2008/4/20 David - CA - Customer B - 2009/3/31 I want to search David - CA and it gets me back with only the latest record as followed: David - CA - Customer B - 2009/3/31 Would you help me again? Thanks. Angus "ryguy7272" wrote: Ya know what, try this, and then you can search for anything: Sub CopyCA() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long Dim dRow As Long Dim sCount As Long sCount = 0 dRow = 0 myword = InputBox("Enter items to search for.") For sRow = 1 To Range("A65536").End(xlUp).Row If Cells(sRow, "A") Like "*" & myword & "*" Then sCount = sCount + 1 dRow = dRow + 1 Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") End If Next sRow MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done" End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Look at this example: http://en.allexperts.com/q/Excel-105...-FIND-COPY.htm Hopefully you learn a thing or two by doing this yourself...I'll give you the modified code just in case you are new to VBA... Sub CopyCA() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Sheet2") Dim sRow As Long Dim dRow As Long Dim sCount As Long sCount = 0 dRow = 0 For sRow = 1 To Range("A65536").End(xlUp).Row If Cells(sRow, "A") Like "*CA*" Then sCount = sCount + 1 dRow = dRow + 1 Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") End If Next sRow MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done" End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Angus" wrote: Hi, i have data like following, which is the salesman responsible for each state until a specific time: David - CA - 2008/1/12 Peter - NY - 2008/5/25 Susan - CA - 2009/3/25 David - NY - 2008/4/20 How do I search "CA" and it will show latest records for each salesman has been working in CA, ie, David - CA - 2008/1/12 Susan - CA - 2009/3/25 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filter criteria | Excel Discussion (Misc queries) | |||
Criteria for Advanced Filter HELP | New Users to Excel | |||
Advanced filter criteria for < | Excel Programming | |||
Advanced filter criteria | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |