multiple criteria

[Patti]

Hello,

I have the following spreadsheet

Last Name First Name ID Date Goal
Doe Jane 123 8/7/08 B
Doe John 124 8/9/08 D

Some rows have the same ID. I would like my results to list unique id's. If
there is the same id in two rows, I would like it to display the row with the
most recent date. If there is the same id in two rows with the same id and
the same date, i would like it to show the row with the highest goal (A being
the highest and so on).

Thank you for your help!

 

[Herbert Seidenberg]

Excel 2007
Pivot Tables
Value Filters
No formulas or code
http://www.mediafire.com/file/zz4ie5mgyyz/01_20_09a.xlsx

 

[Max]

Another play, using only formulae ..
"array-entered" means to press CTRL+SHIFT+ENTER to confirm the formula

Assume table as posted in cols A to E, data from row 2 to 100
(E2:E100 is assumed fully populated with letter grades)

In F2: =IF(C2="","",IF(COUNTIF(C$2:C2,C2)>1,"",ROW()))
In G2: =INDEX(C:C,SMALL(F:F,ROWS($1:1)))
In H2, array-entered: =MAX(IF($C$2:$C$100=G2,$D$2:$D$100))

In I2, array-entered
=INDEX(A$2:A$100,MATCH(MAX(IF(($C$2:$C$100=$G2)*($D$2:$D$100=$H2),ROW($A$2:$A$100))),IF(($C$2:$C$100=$G2)*($D$2:$D$100=$H2),ROW($A$2:$A$100)),0))
Copy I2 to L2. Format L2 as date.

In M2, array-entered
=CHAR(MIN(IF((C$2:C$100=G2)*(D$2$100=H2)*CODE(E$2:E$100)>0,(C$2:C$100=G2)*(D$2$100=H2)*CODE(E$2:E$100))))

Select F2:M2, copy down to M100. Minimize/hide away cols F to H. Cols I to M
should return the required results (from the source data set in A2:E100)
satisfying all of your multiple criteria. Result lines will be neatly packed
at the top, with #Num! errors below (I excluded error trapping here, for
better clarity on what's happening as the core formulas are quite long).

Try the above out, let me know how it went for you.
Pl remember to press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Patti]

Thank you for your reply. I tried it and it populated information in rows I
and L. Rows J and K are blank and row M shows VALUE!. In my example, I left
out that the duplicate person does not list the id. EX:

Last Name First Name ID Date Goal
Doe Jane 123 8/7/08 B
Doe John 124 8/9/08 D
Doe John 8/9/08 B

So the blank row is populating the information for the next row.

 

[Max]

The key source cols ID & Date are presumed/required to be filled for each row
of data. Col E (Goal) must be also be fully pre-populated, otherwise col M
will display #VALUE!. Just pre-fill all new rows in advance in col E with
say, the letter Z. That's the reason behind the earlier caveat line:

(E2:E100 is assumed fully populated with letter grades)

Here's a working sample for easy reference:
http://freefilehosting.net/download/448j2
Multiple complex criteria extracts.xls

Some explanations on What's happening?
1. Cols F & G (together) derives the uniques list of IDs and packs it all up
at the top
2. Col H then gathers the most recent date for the IDs. Since real dates are
numbers, the most recent date = maximum number for the ID

Reading the uniques list of IDs and most recent dates in cols G and H,
3. Cols I to L completes the extract from all source cols, except for col E
(Goal) which needs special treatment (in col M)

4. Col M extracts the "highest" Goal results. CODE is used to convert the
letter grades (A, B, C, etc) to numbers within the conditional MIN (A=65,
B=66, and so on), while the outer CHAR re-converts it back to the letter
grades after MIN evaluates the required lowest numeric equivalent for the ID
and its most recent date (the lowest num = "highest" goal). The source col E
must be fully populated, otherwise col M will display #VALUE!. Just pre-fill
all new rows in advance in col E with say, the letter Z.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---