dave3009
Well-known Member
- Joined
- Jun 23, 2006
- Messages
- 7,142
- Office Version
- 365
- 2021
- Platform
- Windows
- Mobile
- Web
Hi
Hoping someone can help me tidy this up, I need to return the items in column AI where column AJ has a value greater than or equal to 25 and is also the first instance of that value (I've highlighted these in yellow). I have managed to cobble together a working solution which is included here. I'm hoping that someone may be able to simplify it. The values in column AI are fairly immaterial, the important piece is that column AJ is above 25 and the first instance in the column of that value.
If possible I would like this to work in Office 2016, but I do plan to roll it out at some point to M365 so if there is an alternative then I'd really like to see it.
TIA, best regards, Dave
Hoping someone can help me tidy this up, I need to return the items in column AI where column AJ has a value greater than or equal to 25 and is also the first instance of that value (I've highlighted these in yellow). I have managed to cobble together a working solution which is included here. I'm hoping that someone may be able to simplify it. The values in column AI are fairly immaterial, the important piece is that column AJ is above 25 and the first instance in the column of that value.
Book2 | |||||||
---|---|---|---|---|---|---|---|
AH | AI | AJ | AK | AL | |||
1 | |||||||
2 | Apple | 0 | Chocolate | ||||
3 | Pear | 0 | Banana | ||||
4 | Chocolate | 30 | Cherry | ||||
5 | Banana | 25 | Lobster | ||||
6 | Cherry | 27 | |||||
7 | Prawns | 27 | |||||
8 | Lobster | 29 | |||||
9 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AL2:AL8 | AL2 | =IFERROR(INDEX($AI$2:$AI$8,SMALL(IF(FREQUENCY(IF($AJ$2:$AJ$8>=25,MATCH($AJ$2:$AJ$8,$AJ$2:$AJ$8,0)),ROW($AJ$2:$AJ$8)-ROW($AJ$2)+1)>0,ROW($AJ$2:$AJ$8)-ROW($AJ$2)+1),ROWS($A$1:$A1))), "") |
If possible I would like this to work in Office 2016, but I do plan to roll it out at some point to M365 so if there is an alternative then I'd really like to see it.
TIA, best regards, Dave