Shifting Nonblank Cells and Adjusting an Array Formula for the Nonblank Cells

**cmaitlen** · 12-21-2011, 06:19 PM

I searched the web trying to find an answer to find a solution to my excel dilemma, but I could not find an exact solution. I found a few close answers, but not what I need.
I think this might require a macro to do what I am wanting.

I have 3 rows of data (1,2,3). Two rows contain basic entered numbers (1,2). The third row (3) contains a calculation based on the numbers in the same column in the other two rows (e.g. B4=B2+B3, C4=C2+C3, etc.)
I am then using an array formula based on all the numbers in row 2 and the calculated numbers in row 3.

My problem is that I have some blank cells in row 2 causing my array formula not to work for the data I have. I need an automated method of eliminating all the blank cells in row 2 along with the corresponding values in rows 1 and 3 above and below the blank cells. I would like for the columns containing non-blank values to shift left eliminating any colums with a blank value.

The second problem deals with the array formula. I am dealing with a large amount of data and I am not always sure how many or which cells in row 2 may be blank. I want my array formula to use all the nonblank cells in rows 2 and their corresponding values in row 3. This means my array formula will need to change to account for how ever many columns of nonblank data I have available. For example, my array formula may calculate for 10 columns of data, but 4 columns have blank cells causing incorrect results. Therefore, I need all the columns containing a balnk value to be eliminated from the array formula. I assume this could be done by automatically shifting all the columns containing full data to the left and automatically adjusting the array formula for the 6 columns of data rather than 10 columns.

I have attached a sample worksheet. I have simplified my exact formulas into this simplified scenario for better understanding, but the situation is the same.

**daddylonglegs** · 12-21-2011, 08:31 PM

This array formula entered in B14:F14 will give the required results

=LINEST(N(OFFSET(B12:K12,,SMALL(IF(B11:K11<>"",COLUMN(B11:K11)-COLUMN(B11)),ROW(INDIRECT("1:"&COUNT(B11:K11)))),1,1)),N(OFFSET(B11:K11,,SMALL(IF(B11:K11<>"",COLUMN(B11:K11)-COLUMN(B11)),ROW(INDIRECT("1:"&COUNT(B11:K11)))),1,1))^{1,2,3,4})

That will ignore any columns where row 11 is blank......without actually removing or moving any columns. See attached