• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FILTER, SORT and IFERROR nesting issue

sdecadenet

New Member
Any help would be appreciated. This has got me beaten!

I've been working with FILTER and SORT and they are amazing! However I have encountered an issue with cells that contain over 255 characters.

The attached workbook contains a worksheet of data (DATA tab), and two filtered lists.

The Filtered (1) worksheet has an issue when ANDY JONES (cell C1) and HIGH (cell C2) are selected as the two criteria for FILTER. There are no records for Andy Jones, High so I seem to get an error because the SORT function has no data to work with.

So on he Filtered (2) worksheet I added IFERROR to suppress this issue. That works fine until I change the value in C2 to medium. All descriptions over 255 characters appear as BLANK cells! Remove IFERROR and it works fine again but of course the error reappears when HIGH is selected in cell C2.

I have suppressed the error with conditional formatting but I would prefer the solution to be formula based. Any ideas would be appreciated.
 
You could use
=SORT(FILTER(Data!A2:F1001,(Data!B2:B1001=C1)*(Data!C2:C1001=C2),{"","","","","",""}),6,-1)
Which will return one blank row if there is no data.
 
Back
Top