VBA Advanced Filter - latest lines for multi criteria

[Angus]

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

 

[ryguy7272]

Look at this example:
http://en.allexperts.com/q/Excel-1059/EXCEL-VBA-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---

 

[ryguy7272]

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

 

[Angus]

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

 

[Angus]

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

 

[Patrick Molloy]

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]

Got it, thanks.

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