Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple criteria
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple criteria
Excel 2007
Pivot Tables Value Filters No formulas or code http://www.mediafire.com/file/zz4ie5...01_20_09a.xlsx |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple criteria
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:D$100=H2)*CODE(E$ 2:E$100)0,(C$2:C$100=G2)*(D$2:D$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" wrote: 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple criteria
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" wrote: 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:D$100=H2)*CODE(E$ 2:E$100)0,(C$2:C$100=G2)*(D$2:D$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" wrote: 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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple criteria
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 --- "Patti" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |