Most recent data based on cell (date) and list

[swicked]

I have a spreedsheet which tracks a teams performance.
There are multiple entries for TEAM each with different date, number, subject AS SEEN BELOW


A1 B1 B2 B3
TEAM DATE NUMBER SUBJECT
MM03 01/20/14 1620744 DROP
MIM01 08/20/14 2418963 TEAR
MWP 09/05/14 2486589 ERROR
MM03 12/14/14 2856987 CAR
OP2 07/23/14 2158965 TRUCK
MWP 03/15/14 2058965 BOAT
SC05 04/05/14 1425684 BIKE
MM03 05/10/14 1619677 FALL
MM04 05/01/14 2158698 SLIP



I'd like to return the most recent date, number, and subject for each team in a list for ex:
MIM01 08/20/14 2418963 TEAR
MM03 12/14/14 2856987 CAR
OP2 07/23/14 2158965 TRUCK
MWP 09/05/14 2486589 ERROR
SC05 04/05/14 1425684 BIKE
MM04 05/01/14 2158698 SLIP


Any help / suggestions would be greatly appreciated

 

[MickG]

Try this for results starting "F1".

[COLOR="Navy"]Sub[/COLOR] MG07Jan03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) > .Item(Dn.Value).Offset(, 1) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ReDim Ray(1 To .Count, 1 To 4)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    [COLOR="Navy"]For[/COLOR] n = 1 To 4
        Ray(c, n) = .Item(K).Offset(, n - 1).Value
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] K
Range("F1").Resize(.Count, 4) = Ray
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[swicked]

how do I apply the code?

I've not dug that deep into excel vba (tyically i've used access)

 

[MickG]

Try this:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

 

[swicked]

works great
thanks

Now i'll hack it apart as my data set has much more info to sort through

 

[MickG]

You're welcome