Return Unique List Based on Criteria

[dave3009]

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.

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

 

[kweaver]

The following seems to work in 365, but I haven't come up with something that could be used in 2016 as yet.

Book1
	AI	AJ	AK	AL
2	Apple	0		Banana
3	Pear	0		Cherry
4	Chocolate	20		Lobster
5	Banana	25		Tomato
6	Cherry	27
7	Prawns	27
8	Lobster	29
9	Tomato	50
10	Orange	50
Sheet1

Cell Formulas
Range	Formula
AL2:AL5	AL2	=INDEX(AI2:AI10,MATCH(FILTER(UNIQUE(AJ2:AJ10),25<=UNIQUE(AJ2:AJ10)),AJ2:AJ10,0))
Dynamic array formulas.

 

[Marcelo Branco]

Another - not so simple, but it works in Excel 2016

Pasta3
	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
Plan7

Cell Formulas
Range	Formula
AL2:AL8	AL2	=IFERROR(INDEX(AI$2:AI$8,AGGREGATE(15,6,(ROW(AI$2:AI$8)-ROW(AI$2)+1)/((AJ$2:AJ$8>=25)*(MATCH(AJ$2:AJ$8,$AJ$2:$AJ$8,0)=ROW(AJ$2:AJ$8)-ROW(AJ$2)+1)),ROWS(AL$2:AL2))),"")

M.

 

[alansidman]

A Power Query Solution that should work in both versions

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Column2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each if[Column2]>=25 then [Column1] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Other Columns"

 

[Peter_SSs]

Another 365 formula option if you have the LET function.

21 07 29.xlsm
	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
List

Cell Formulas
Range	Formula
AL2:AL5	AL2	=LET(r,AJ2:AJ8,FILTER(AI2:AI8,(r>=25)*(MATCH(r,r,0)=SEQUENCE(ROWS(r)))))
Dynamic array formulas.

 

[dave3009]

Thanks everyone for your fine suggestions. Apologies for the delayed response I had a bit of a busy day.

Kind regards, Dave

 

[Peter_SSs]

You're welcome. Glad we could help.