Retrieving Data in rows without spaces

[ExcelRoy]

Hi,
I am trying to find a way of listing data from Column DW if column ED is Greater than 0 and column EK is "ABC"

I would ideally need the data to be under each other rather than on separate rows

Hope this makes sense

Many thanks

 

[Fluff]

How about
=IFERROR(INDEX($DW$2:$DW$31,AGGREGATE(15,6,ROW($DW$2:$DW$31)-ROW($DW$2)+1/(($ED$2:$ED$31>0)*($EK$2:$EK$31="abc")),ROWS($1:1))),"")

 

[ExcelRoy]

Hi Fluff,

many thanks for the help

What is the AGGREGATE(15,6 meaning please

 

[Fluff]

You're welcome & thanks for the feedback

Aggregate(15 is the same as Small & the 6 tells it to ignore errors.
For more info have a look here https://exceljet.net/excel-functions/excel-aggregate-function

 

[ExcelRoy]

Hi Fluff,

Awesome thanks

Would there be a 2007 option for this formula as I assume this only works on 2010 onwards

Thanks

 

[Fluff]

How about
=IFERROR(INDEX($DW$2:$DW$31,SMALL(IF(($ED$2:$ED$31>0)*($EK$2:$EK$31="abc"),ROW($DW$2:$DW$31)-ROW($DW$2)+1),ROWS($1:1))),"")

This is an array formula & needs to be confirmed with Ctrl Shift Enter, rather than just Enter

 

[ExcelRoy]

That's perfect, thanks Fluff

Just one last ask if may

Could I add another condition to this ie "DEF" in column EK

Thanks again

 

[Fluff]

Use
($EK$2:$EK$31={"abc","def"})

 

[ExcelRoy]

Thanks Fluff, can these be cell references instead of "abc"

 

[Fluff]

No, youl'd need to change it to
IF(($ED$2:$ED$31>0)*(($EK$2:$EK$31=$K$1)+($EK$2:$EK$31=$L$1))