Exclude Blank Rows With Array

[billgyrotech]

I have been trying to accomplish something. Include only rows with data and moving them up so there aren't any blank rows between from F2:I9 on the Summary worksheet. What am I doing wrong?

I appreciate any help or suggestions.

[JeteMc]

Here is an array entered formula that will populate column F:

=IF(ROWS(F$1:F1)>COUNT(A$2:A$13),"",INDEX(A$2:A$13,SMALL(IF(A$2:A$13<>"",ROW($A$1:$A$12)),ROW(A1))))

The following formula (regular) can then be pasted into G2 and copied down and across:

=INDEX(B$2:B$9,MATCH($F2,$A$2:$A$9,0))

Let us know if you have any questions.

[billgyrotech]

Thank you that works great! I have a question in order to learn what is going on. What if I insert a row above the tables in the 'Summary' worksheet?

I am trying to understand how the codes work.

Thank you for the help.

[JeteMc]

Modify the array entered formula in F2 as follows:

=IF(ROWS(F$1:F1)>COUNT(A$2:A$13),"",INDEX(A$2:A$13,SMALL(IF(A$2:A$13<>"",ROW($A$1:$A$12)-MIN(ROW(A$1:A$12))+1),ROWS(A$1:A1))))

Once activated, by simultaneously pressing Ctrl, Shift and Enter while F2 is still in edit mode, copy down to F9. After a new row one is inserted in the spreadsheet the formula will still display the correct number which the Index/Match formulas in the corresponding columns will use. To see how the formula handles the additional row run Evaluate Formula from the Formulas tab.
Let us know if you have any questions.

[billgyrotech]

Not sure how but I played with the code and came up with this which seems to work. I put this in F3 and copied down.

=IF(ROWS(F$1:F1)>COUNT(A$2:A$13),"",INDEX(A$2:A$12,SMALL(IF(A$3:A$13<>"",ROW($A$2:$A$12)),ROW(A1))))

[JeteMc]

I am glad that you have a formula that does what you need. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.