Greetings All, I'm needing a little help please. I have a spreadsheet which is needing to be a 'rolling 30 day' report of agents. On the raw data tab, column A lists dates, and column B lists agent's names. Column A will have 40+ days of values but I'm needing only the most recent 30 days of values. For example, if cell F1="Jane Doe", find the bottom most date for Jane Doe in column B, and output the date listed next to her in column A. Then the next row would list the 2nd bottom date for Jane Doe, and so on.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
lookup for bottom 3 entries
- Thread starter DDT123
- Start date
Crystalyzer
Well-known Member
- Joined
- Oct 18, 2011
- Messages
- 690
- Office Version
- 365
- Platform
- Windows
| Book6 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Date | Name | Jane Doe | 7/7/2021 | 7/12/2021 | 7/19/2021 | |||||
| 2 | 6/15/2021 | ||||||||||
| 3 | 6/16/2021 | ||||||||||
| 4 | 6/17/2021 | ||||||||||
| 5 | 6/18/2021 | ||||||||||
| 6 | 6/19/2021 | ||||||||||
| 7 | 6/20/2021 | Jane Doe | |||||||||
| 8 | 6/21/2021 | ||||||||||
| 9 | 6/22/2021 | ||||||||||
| 10 | 6/23/2021 | ||||||||||
| 11 | 6/24/2021 | ||||||||||
| 12 | 6/25/2021 | ||||||||||
| 13 | 6/26/2021 | Jane Doe | |||||||||
| 14 | 6/27/2021 | ||||||||||
| 15 | 6/28/2021 | ||||||||||
| 16 | 6/29/2021 | ||||||||||
| 17 | 6/30/2021 | ||||||||||
| 18 | 7/1/2021 | Jane Doe | |||||||||
| 19 | 7/2/2021 | ||||||||||
| 20 | 7/3/2021 | ||||||||||
| 21 | 7/4/2021 | ||||||||||
| 22 | 7/5/2021 | ||||||||||
| 23 | 7/6/2021 | ||||||||||
| 24 | 7/7/2021 | Jane Doe | |||||||||
| 25 | 7/8/2021 | ||||||||||
| 26 | 7/9/2021 | ||||||||||
| 27 | 7/10/2021 | ||||||||||
| 28 | 7/11/2021 | ||||||||||
| 29 | 7/12/2021 | Jane Doe | |||||||||
| 30 | 7/13/2021 | ||||||||||
| 31 | 7/14/2021 | ||||||||||
| 32 | 7/15/2021 | ||||||||||
| 33 | 7/16/2021 | ||||||||||
| 34 | 7/17/2021 | ||||||||||
| 35 | 7/18/2021 | ||||||||||
| 36 | 7/19/2021 | Jane Doe | |||||||||
| 37 | 7/20/2021 | ||||||||||
| 38 | 7/21/2021 | ||||||||||
| 39 | 7/22/2021 | ||||||||||
| 40 | 7/23/2021 | ||||||||||
| 41 | 7/24/2021 | Jane Doe | |||||||||
| 42 | 7/25/2021 | ||||||||||
| 43 | 7/26/2021 | ||||||||||
| 44 | 7/27/2021 | ||||||||||
| 45 | 7/28/2021 | ||||||||||
| 46 | 7/29/2021 | ||||||||||
| 47 | 7/30/2021 | ||||||||||
| 48 | 7/31/2021 | ||||||||||
| 49 | 8/1/2021 | ||||||||||
| 50 | 8/2/2021 | ||||||||||
| 51 | 8/3/2021 | Jane Doe | |||||||||
| 52 | 8/4/2021 | ||||||||||
| 53 | 8/5/2021 | ||||||||||
| 54 | 8/6/2021 | ||||||||||
| 55 | 8/7/2021 | ||||||||||
| 56 | 8/8/2021 | ||||||||||
| 57 | 8/9/2021 | ||||||||||
| 58 | 8/10/2021 | ||||||||||
| 59 | 8/11/2021 | ||||||||||
| 60 | 8/12/2021 | ||||||||||
| 61 | 8/13/2021 | ||||||||||
| 62 | 8/14/2021 | ||||||||||
| 63 | 8/15/2021 | ||||||||||
| 64 | 8/16/2021 | ||||||||||
| 65 | 8/17/2021 | ||||||||||
| 66 | 8/18/2021 | ||||||||||
| 67 | 8/19/2021 | ||||||||||
| 68 | 8/20/2021 | ||||||||||
| 69 | 8/21/2021 | ||||||||||
| 70 | 8/22/2021 | ||||||||||
| 71 | 8/23/2021 | ||||||||||
| 72 | 8/24/2021 | ||||||||||
| 73 | 8/25/2021 | ||||||||||
| 74 | 8/26/2021 | ||||||||||
| 75 | 8/27/2021 | ||||||||||
| 76 | 8/28/2021 | ||||||||||
| 77 | 8/29/2021 | ||||||||||
| 78 | 8/30/2021 | ||||||||||
| 79 | 8/31/2021 | ||||||||||
| 80 | 9/1/2021 | Jane Doe | |||||||||
| 81 | 9/2/2021 | ||||||||||
Sheet2 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G1 | G1 | =SMALL(IF(($B$2:$B$81=$F$1) * ($A$2:$A$81>=TODAY()-30), $A$2:$A$81), 1) |
| H1 | H1 | =SMALL(IF(($B$2:$B$81=$F$1) * ($A$2:$A$81>=TODAY()-30), $A$2:$A$81), 2) |
| I1 | I1 | =SMALL(IF(($B$2:$B$81=$F$1) * ($A$2:$A$81>=TODAY()-30), $A$2:$A$81), 3) |
Upvote
0
Similar threads
- Question
