Advanced filter - complex criteria question

[GarysBeau]

Hi again.

With this query im not entirely sure if the advanced filter section is the best option for me, so in that respect im open to suggestions.

Ok I have a table like this:

Date-----Site------
1998-----M1-------
2005-----M1-------
2010-----M1-------
2004-----M2---------
2000-----M2--------
2008-----M5--------
2010-----S1-------
2006-----100-------


I basically want to filter so that I extract all those sites that begin with 'm' and only display the latest year/date with duplicate sites. So I should extract the following:

2010------M1
2004------M2
2008------M5

Is there a way to do this? Ive been following the advanced filter function and can extract all the 'm's but can't seem to only get those that display the lastest date/year

Any help much appreciated

 

[Ron Coderre]

It's really important that you post the version of Excel you're using so we can make better suggestions.

In the meantime, you might be able to use a pivot table to display:
• Sites beginning with "M"
• Max of Date for those sites

Does that help?

 

[GarysBeau]

Sorry I made that mistake before. Im using excel 2003.

There's a lot of other columns that I need but are not relevant in the filtering process (anbd thats why I thought a pivot table wouldnt be useful as I thought it wouldnt show the numerous other information, that I need to display)

 

[GarysBeau]

Not sure if that helps.

Basically I need to filter out the information that I dont want to see but want to retain the information in its present form (including all the other fields of data, which there is lots).

I presumed the advanced filter would be the best way of doing this, as I assumed (perhaps wrongly) that pivot tables help to mainpulate sections of an existing list (adding, counting etc) but cant display the full extent of a list.