Extract non-blank data from a list

[jlb333333]

Hi everyone.
I am using the following array formula to compact a list by removing the open rows.
=IFERROR(INDEX(HN4:HN5740, SMALL(IF((HN4:HN5740)="","", ROW(HN4:HN5740)-MIN(ROW(HN4:HN5740))+1), ROW(HN1))),"")

It works brilliantly on a short list.

However, my list is actually 7300 rows long(formula shows 5740) and my reasonably up to date PC with Excel 2013 calculates the result so slowly that it is an unuseable option.

Is there another way to do this?



JLB

 

[XOR LX]

Hi.

To how many rows do you typically copy your formula so that you ensure that you capture all possible returns?

Regards

 

[Aladin Akyurek]

It's a non-robust and a bit costly set up. If performance is needed...

Where do you want to have the result list exactly?

 

[jlb333333]

Hi XOR LX.
Thank you for the reply.
I am copying the array formula down the full 7300 rows.(in my example, 5740 rows)

 

[jlb333333]

Hi Aladin.
Thank you for your reply.

I need a list with only filled cells for linking to another worksheet from which I am exporting the result to a text file to import it into a complex but very efficient Fortran program. The text file has to be 100% predictable in format.

 

[Aladin Akyurek]

Hi Aladin.
Thank you for your reply.

I need a list with only filled cells for linking to another worksheet from which I am exporting the result to a text file to import it into a complex but very efficient Fortran program. The text file has to be 100% predictable in format.

In which cell did you enter that IFERROR formula and copied down?

 

[jlb333333]

In which cell did you enter that IFERROR formula and copied down?

I copied the formula into a column next to the list with the blank rows to be excluded so that from the top down I would have cells with valid contents to read from.

 

[Aladin Akyurek]

I copied the formula into a column next to the list with the blank rows to be excluded so that from the top down I would have cells with valid contents to read from.

HO3: 0

HO4, copied down:

=IF(HN4="","",LOOKUP(9.99999999999999E+307,$HO$3:HO3)+1)

HP3:

=LOOKUP(9.99999999999999E+307,HO:HO)

HP4: #No blanks list#

HP5, copied down:

=IF(ROWS($HP$5:HP5)<=$HP$3,LOOKUP(ROWS($HP$5:HP5),HO:HO,HN:HN),"")

This set up trades off cell space (memory) against speed.

 

[billszysz]

I don't understand... why do not you use a filter or advanced filter or Pivot Table or Power Query... Why you need to have a formula. Probably my english is to poor to understand the reason

 

[jlb333333]

Thank you Aladin.
The data in HN are numbers on text format.
I am getting #NA in all nonblank cells. Is that the reason?
They are NOT array formulas, are they?
Many thanks for your effort.
I hope I can learn something here.
Thanks again.