Copy rows from another sheet ignoring blank rows

Hi

I'm trying to fill out a fixture list, copied from data on another sheet but I want to do it without the blank rows. The space between the rows is a uniform 2 spaces except when the date changes and then there is a 3 row space so I'm struggling to get the data where I want it. I've attached a sample to highlight where I want the data.

Any help much appreciated.

Try

in B3

=IFERROR(INDEX(Data!A$1:A$1000,SMALL(IF(Data!$A$1:$A$1000<>"",ROW(Data!$A$1:$A$1000)-ROW($A$1)+1,""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

THEN copy across and down

format Column C as time (hh:mm)

Cheers John, a great help as usual

Many Thanks

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

It appears I have no clue what I'm doing.

I've attached a new example with the actual sheet names I use and the web query data I need to manipulate in to the fixtures list.

I've colour coded what I need where from 'England 1' to 'Data' and as per, any help appreciated.

In AQ3
=IFERROR(INDEX('England 1'!CE$1:CE$1000,SMALL(IF('England 1'!$CF$1:$CF$1000<>"",ROW('England 1'!$A$1:$CF$1000)-ROW($CF$1)+1,""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across and down

That's done the trick. Thanks for your patience.