Filter data based on multiple 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

 

[Guest]

Try a Pivot Table:

First, make sure your table has column headings
(exampe: A1: MyNumber, B1: MyDate)

<Data><Pivot Table>
Use: Excel
Select your data range
Click the [Layout] button

ROW: Drag the MyNumber field here
DATA: Drag the MyDate field here
Dbl-click it and set it to Max
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each MyNumber and the Max of dates for each MyNumber.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[John.Forr]

Yea, that worked, but now here's the next problem...I have about 20
columns after the first two columns, that I will also need to display
for that combination of column A&B. How can I do this with a
pivottable?

 

[Guest]

See if this array formula* approach works for you:

Assumptions:
Your data table is in A1:V10
Your pivot table begins in cell A20 and extends through B?? (depending on
returned values)

So...A20: Max of MyNumber
A21: MyNumber
B21: Total

C21: MyField_1
C22:
=INDEX($C$1:$C$10,MATCH($A22&"_"&TEXT($B22,"yyyymmdd"),$A$1:$A$10&"_"&TEXT($B$1:$B$10,"yyyymmdd"),0))

Copy from C22 to C23 and down as far as needed
Copy the Col_C formulas to Col_D and as far to the right as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Those formulas return the data associated with each unique MyNumber and the
corresponding Max of MyDate for that MyNumber.

Sample returned values (with my test data):
Max of MyDate
MyNumber_____Total____Field_1__Field_2_____Field_3
1_________10/11/05_c101_______c101_______c101
2_________01/04/06_c10001_____c10001_____c10001
3_________08/26/05_c1000001___c1000001___c1000001
4_________11/04/05_c100000001_c100000001_c100000001

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[John.Forr]

Thanks, that worked perfectly. I ended up adding in a dynamic named
range for my pivottable, then hid the sheet where the table resides. I
added a macro for the end user to update the pivottable, without them
needing to know that it was even there. For the arrays, I added a
dynamic named range for each of my columns, so that they would update
accordingly when new rows are added.