Return all non-blank cells from a range

[Mecha_Trueno]

Hi,

I want to apply pb71's solution but horizontally instead. I tried applying the same formula except I inverted the rows/columns etc but it doesn't seem to be working. (Please see attached)

Does anyone know what I'm soing wrong here?

Thanks.

[XOR LX]

Hi,

You haven't correctly adjusted the COLUMN part at the end. In the original (vertical) version, the function of the ...+ROW(D1) is to give a value of 1 (since ROW(D1)=1) and then, when copied down, to give successively ...+ROW(D2) (=2), ...+ROW(D2) (=3), etc., etc.

So when you changed this to a 'horizontal' version, you correctly substituted the COLUMN function for the ROW function, but you forgot to ensure that, for your first case, this would return a value of 1. For this, you simply need to ensure that you have a reference in the COLUMN function which is in column A, e.g. A1:

=IFERROR(INDEX(19:19,,SMALL(INDEX(NOT(ISBLANK($K$19:$AC$19))*COLUMN($K$19:$AC$19),,0),COUNTBLANK($K$19:$AC$19)+COLUMN(A1))),"")

When dragged to the right, this will then produce ...+COLUMN(B1) (=2), ...+COLUMN(C1) (=3), etc., etc.

Regards

[Tony Valko]

Not real clear what you want to do.

I'm guessing that you want to take the data in the horizontal range K19:AA19 and rearrange it into a contiguous vertical range?

If so, try this...

This array formula** entered in H2:

=IFERROR(INDEX($19:$19,SMALL(IF(K$19:AA$19<>"",COLUMN(K$19:AA$19)),ROWS(H$2:H2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Mecha_Trueno]

Hi,

You haven't correctly adjusted the COLUMN part at the end. In the original (vertical) version, the function of the ...+ROW(D1) is to give a value of 1 (since ROW(D1)=1) and then, when copied down, to give successively ...+ROW(D2) (=2), ...+ROW(D2) (=3), etc., etc.

So when you changed this to a 'horizontal' version, you correctly substituted the COLUMN function for the ROW function, but you forgot to ensure that, for your first case, this would return a value of 1. For this, you simply need to ensure that you have a reference in the COLUMN function which is in column A, e.g. A1:

=IFERROR(INDEX(19:19,,SMALL(INDEX(NOT(ISBLANK($K$19:$AC$19))*COLUMN($K$19:$AC$19),,0),COUNTBLANK($K$19:$AC$19)+COLUMN(A1))),"")

When dragged to the right, this will then produce ...+COLUMN(B1) (=2), ...+COLUMN(C1) (=3), etc., etc.

Regards

Thanks for your help!
Now why didn't that occur to me...

[Mecha_Trueno]

Is there a way I can mark the thread as 'solved'? Or is this for the administrator?

[Tony Valko]

For this, you simply need to ensure that you have a reference in the COLUMN function which is in column A, e.g. A1:

=IFERROR(INDEX(19:19,,SMALL(INDEX(NOT(ISBLANK($K$19:$AC$19))*COLUMN($K$19:$AC$19),,0),COUNTBLANK($K$19:$AC$19)+COLUMN(A1))),"")

When dragged to the right, this will then produce ...+COLUMN(B1) (=2), ...+COLUMN(C1) (=3), etc., etc.

Regards

Better to use the COLUMNS or ROWS function (whichever applies to the particular application).

You would use the reference of the first cell the formula is entered in.

COLUMNS($D2:D2)
COLUMNS($X27:X27)

ROWS(D$2:D2)
ROWS(X$27:X27)

Using the COLUMNS/ROWS function makes the formula a bit more robust without adding any undesirable inefficiency.

Using your formula with the COLUMN function, insert a new column A and see what happens to the results.

[XOR LX]

Thanks, Tony, but this was not actually 'my' formula and I was merely pointing out to the user the error that he'd made when attempting to adapt the formula he'd taken from another link (in which the ROW, not ROWS method was used).

Having said that, I possibly could have spent a bit more time on the thread and perhaps included something similar to that which you've just explained (of which I'm fully aware), but since you've just done so I don't need to now!

Cheers

[Tony Valko]

Just tryin' to help!

[XOR LX]

I know! And I'm grateful! Just tired so not seeing as much as you