Formula to extract non blank data so that there are no blank rows betwwen the visible data

[Howardc1001]

I have formula n Col D to extract data. Where there is no match, then formula will insert a blank

I would like a formula in Col F to show the visible items that are Col D one below each each


I have attached sample data


Your assistance is most appreciated

[GeoffW283]

In F2:

Please Login or Register  to view this content.

Increase "20" as necessary to cover your data.
This needs to be entered into F2 as an array formula via CTRL-SHIFT-ENTER
Copy the formula down as far as necessary.

Hope this helps!

[Howardc1001]

Thanks for the help Geoff

Please explain how this formula works from aggregate part . I am familiar with index, but with aggregate and Row

[JeteMc]

You could also reduce the volatility of the spreadsheet by rearranging Geoff's formula to read:

Please Login or Register  to view this content.

The above formula may be activate by pressing the Enter key.
As to how AGGREGATE works perhaps the link below can explain.
https://support.microsoft.com/en-us/...6-e19993fa26df
I'd also encourage you to select cell F4 and utilize the Evaluate Formula feature (Formulas tab).
Let us know if you have any questions.

[BMV]

Please Login or Register  to view this content.

[GeoffW283]

Thanks for the help Geoff
Please explain how this formula works from aggregate part . I am familiar with index, but with aggregate and Row

JeteMc has addressed you question on aggregate() - as well as supply a simpler and better solution than mine.

You also asked about row(). ROW(A$2:A$20) simply returns a vertical array: {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
which, after a bit more manipulation, becomes the third parameter to aggregate()

The final ROW(A1) serves a different purpose. On the first row this returns 1, but as you drag the formula downwards you get ROW(A2) which evaluates to 2 then 3, 4, 5 etc. This is exactly what is needed for the fourth parameter to the aggregate() function.

Hope this helps! Oh, and thanks for the rep

[Howardc1001]

Thanks for the reputation