Unique description List

[tlc53]

Hi there,

I'm trying to create a unique list of all items which remain unresolved/does not have a resolved date allocated to it.

{=IFERROR(INDEX($B$15:$B$19,SMALL(IF($D$15:$D$19<>"",ROW($B$15:$B$19)),
ROW(1:1))-1,1),””)}


In column B I have the description (B15:B19)
In column D there is a date if the item has been resolved.

I would like the description of column B to appear from B47 down, if it does not have a date assigned to it in column D.

Can anyone see where I am going wrong with this formula? It currently returns #NAME?

Thanks!

 

[Toadstool]

Hi TLC53,

Your ROW calculation will return 15 to 19 but your INDEX is only 5 rows so you're getting a #REF error.
The IFERROR fails because the double quotes aren't double quotes:



You could avoid the array formula by using the SMALL function of AGGREGATE

Book1
	B	C	D
15	Battery flat
16	Fuse blown		2/2/2020
17	Switch broken
18	Dry solder		1/1/2020
19	Capacitor blown
20
46
47	Battery flat
48	Switch broken
49	Capacitor blown
50
51
Sheet1

Cell Formulas
Range	Formula
B47:B51	B47	=IFERROR(INDEX($B$15:$B$19,AGGREGATE(15,6,ROW($B$15:$B$19)-ROW($B$15)+1/($D$15:$D$19=""),ROWS(B$47:B47))),"")

 

[tlc53]

Hi TLC53,
=IFERROR(INDEX($B$15:$B$19,AGGREGATE(15,6,ROW($B$15:$B$19)-ROW($B$15)+1/($D$15:$D$19=""),ROWS(B$47:B47))),"")

Perfect!! Thank you so much! It's doing exactly what I wanted it to.
Really appreciate your help and your beautifully presented answer

 

[Toadstool]

You're welcome!
Thanks for the response.