Auto sort and remove blank cells

[lstevenson]

Hi

I have attached a copy of a SS I am trying to get sorted. In the first sheet data will be copied and pasted each month, I than have two tables that extract the data, that I need. I then would like in the mail sheet to have a formula that extracts the data from column AV:BL in the first sheet, but excludes where the insurance comp = 52 and removes blank cells.
I have the following formula, but this does not remove the blank cells. {=iferror(index(a2:q30,small(if(d2:d30<>52,row(a2:q30)-1,rows($1:1)),columns($a:a,"")} this is based if the table was in columns a to q, currently it is in AV:BL.
The whole idea is that a person copies and pastes the data in the first sheet, then the mail sheet automatically updates for a mail merge

[Phuocam]

Try this in A2:

=IFERROR(INDEX(Sheet3!AV:AV,SMALL(IF((Sheet3!$AY$18:$AY$31<>52)*
(Sheet3!$AY$18:$AY$31<>""),ROW(Sheet3!$AV$18:$BL$31)),ROWS($18:18))),"")

Enter with Ctrl+Shift+Enter.

[FDibbins]

I have attached a copy of a SS I am trying to get sorted. In the first sheet data will be copied and pasted each month

Is that being pasted side-by-side? And if so, why, and why not 1 below the other?

Anyway, try this...
=IFERROR(INDEX(Sheet3!AV:AV,SMALL(IF((Sheet3!$AY$18:$AY$31<>52)*(Sheet3!$AY$18:$AY$31<>""),ROW(Sheet3!$AV$18:$BL$31)),ROWS($1:1))),"")
CSE array-entered

It doesnt sort, but that would be the next step

[TudyBTH]

Try this array formula

Please Login or Register  to view this content.

[lstevenson]

thanks everyone, works great