Complex macro...selection rows based on values

[waxb18]

Hi guys,

Basically its a little tricky.
I have data in one tab and a table where im countifing upon certain variables to distinguish the good data from the bad data.

An example is Mr X:

Summary tab:

Name Good Bad Total
Mr X ...5..... 3 ....8

Now what i would like to do is seperate the Bads from the goods (the bads being the most recent 3 jobs) and then put them in a seperate sheet


Name ....Place...........Date

MR X .....London........13/10/11
MR X..... B'Ham.........12/10/11
MR X......London.......12/10/11
MR X .....London........11/10/11
MR X .....B'ham..........11/10/11
MR X .....Liverpool.......10/10/11
MR X .....Newcastle.....9/10/11
MR X .....Manchester....9/10/11

The bad ones in the case of Mr X being the jobs on 12th and 13th October.

Any ideas????

 

[MickG]

Try this :-
The code assumes the Data comprises all one name i.e:- "Mr X"
Results sheet "Summary"

[COLOR="Navy"]Sub[/COLOR] MG03Nov25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Fst [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Snd [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 3)
Fst = Application.Large(Rng.Offset(, 2), 1)
Snd = Application.Large(Rng.Offset(, 2), 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) = Fst Or Dn.Offset(, 2) = Snd [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("summary")
    .Range("A1").Resize(, 4) = Array("Name", "Good", "Bad", "Total")
    .Range("A2") = Rng(1): .Range("B2") = Rng.Count - nRng.Count: .Range("C2") = nRng.Count: .Range("D2") = Rng.Count
    .Range("A3").Resize(, 3) = Array("Name", "Place", "Date")
    nRng.Resize(, 3).Copy .Range("A4")
[COLOR="Navy"]End[/COLOR] With
nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick