Filter without use of array?

Hi,

I have an example (attached) Whereby a formula can filter out gaps in the data to consolidate them in another column.

The formula works by use of an array, and is pulled down

{=IFERROR(INDEX($A$5:$A$991, MATCH(0, COUNTIF($B$4:B4,$A$5:$A$991), 0)),"")}

I got this array formula by your good selves on this website. Unfortunately I use this too much and on a data set that's too large which has slowed by spreadsheet down. Is it possible to do the same thing without an array? I don't mind the use of helper columns etc, just need to get my spreadsheet working a little quicker

Recently upgraded from 2007 to 365 so any tips on what the new version does would also be appreciated

Best
Sean

You can use column B as a helper, with this formula in B5:

=IF(A5="","-",MAX(B$4:B4)+1)

Copy this down as far as you like. Then you can use this formula in C5:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

Copy down until you get blanks.

Hope this helps.

Pete

MY MAN!!!!!!!!

Thank you so much!!

On small issue,

What if there are repeats? Can it account for that?

Do you not want to get repeats? i.e. you want a unique list? If so, use this in B5:

=IF(A5="","-",IF(COUNTIF(A$5:A5,A5)=1,MAX(B$4:B4)+1,"-"))

then copy down.

Hope this helps.

Pete

You are a genius

Thank you Pete

You're welcome - thanks for the rep.

Strictly speaking, you don't need the hyphens in the formula in column B, and could just have "" (twice), but I like to see where a formula is active.

Pete