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

 

[jlb333333]

Thank you for your comment billszysz.
The data must be saved by an inexperienced user. Everything must work without input or with the absolute minimum of input.
Thanks again.

 

[Aladin Akyurek]

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.

No, they are not array formulas. That's exactly the point. And whether the input is text or not is irrelevant.

Here is the set up: https://dl.dropboxusercontent.com/u/65698317/jlb333333 no-blanks list.xlsx

 

[jlb333333]

Hi Aladin.
One more thing.
HN is not in any numeric or alphabetic order.
Is this relevant?

 

[Aladin Akyurek]

Hi Aladin.
One more thing.
HN is not in any numeric or alphabetic order.
Is this relevant?

No, not at all. See post #12.

 

[jlb333333]

Hi Aladin.
Thank you very much for your input and effort.
It seems to be providing all the answers as you designed it to do.
I will have to study the formulas further to fully understand it but I can see it is working, and with very little processing time.
Thank you again.
I appreciate it very much.
If I could give you a hug, I would.
JLB

 

[Aladin Akyurek]

Hi Aladin.
Thank you very much for your input and effort.
It seems to be providing all the answers as you designed it to do.
I will have to study the formulas further to fully understand it but I can see it is working, and with very little processing time.
Thank you again.
I appreciate it very much.
If I could give you a hug, I would.
JLB

You are welcome. Thanks for the kind feedback.