does anyone know how to drag from the first bottom cell to the right without having empty or 0 cells but having a,b,c,d cells following each other?

I have this top array in which each cell consists of some merged columns. when I write =O9 in another cell and drag it to the right, it gives me some empty cells which I don't want. But how to drag it and fill the cells like having an a in the first cell and the b in the next and so on?

Solution 1:

I'm not sure why your column headers are showing in reverse. Perhaps this is a culture or regional setting but I've never seen that before.

Anyway, you can use something like this:

In cell R19, I have this formula:

=IF(R8=0,Q19,R8)

This says

if R8, i.e. the value in row 8 of this column, is zero (i.e. blank), then use the value in the cell to the left of this cell, otherwise use the value from row 8.

This will also work:

=IF(ISBLANK(R8),Q19,R8)

EDIT:

To get your required output, you can use this:

=FILTER($R$8:$Y$8,$R$8:$Y$8<>"")