Rows to Columns- Non Pattern

[hiker100]

I am trying to pick up all non-blank rows in column D between sections called "Europe" in column A. The number in C is basically the first non-blank row after each Europe.It resets itself when it gets to the next Europe. Is there a way to do this like the solution starting in row 29?

[beaunydal]

Try these array formulae...

Please Login or Register  to view this content.

in A30 filled down as far as required.

Please Login or Register  to view this content.

in B30 filled across to E30 and down as far as required.

Beau Nydal

[hiker100]

Yes, it works great. It is a very dynamic formula. They only thing I neglected to say that I would be using Excel 2007 with this formula, so I could use the iferror function to trap the errors, which would cut the formula length nearly in half. I will mention the Excel version I am using in future. Would the formula stay the same other than adding the iferror function?

[beaunydal]

I don't have XL 2007 so I can't test with IFERROR.
My guess is that

Please Login or Register  to view this content.

in A30 etc and

Please Login or Register  to view this content.

in B30 etc would be the 2007 equivalents.

Beau Nydal

[hiker100]

Thank you so much. This is very helpful.