Filter based on combination of columns

[John.Forr]

I would like to see the unique values from column A, where Column B is
a max() for that value in A. For example, in Column A there are
several 1's, I would like to see the max of column B, where column A is

a 1, and the same thing repeated for all the other unique values in
column A.

INPUT:


A B
1 07/22/05
1 08/26/05
1 10/11/05
2 11/04/05
2 01/04/06
2 07/22/05
3 08/26/05
4 10/11/05
4 11/04/05


The output I'm looking for would look like this:


A B
1 10/11/05
2 01/04/06
3 08/26/05
4 11/04/05

 

[Dave Peterson]

Sounds like a nice reason to learn about pivottables.

Select your range (Include one row of headers)
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the header for column A to the row field
drag the date header button to the data field
double click on that button and choose Max

And finish up the wizard.


If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx