Need a formula that will remove all the leading blank cells in a row

[stephleroux]

Hi All


This is my First post so please bear with me


I have table structured like this:


month 1 month 2 month 3 month 4

q1 1 2 3 4

q2 blank 1 2 3

q3 blank blank 1 2


It contains GDP estimates for each quarter. The estimates are revised each month. What I want is a table that gives me all the 1st estimates in one column followed by all the second estimates in the next column.


Therefore I need to restructure it to look like this:


1st 2nd 3rd 4th

q1 1 2 3 4

q2 1 2 3 blank

q3 1 2 blank blank


I have success with getting first column using a formula that returns the first numeric value in a row. However, that does not help much with the rest of the table.


I have also seen posts on how to delete the blank cells. This works but it is not a solution as I have many large datasets and a manual option will be very time consuming.


Any help would be much appreciated.

 

[Colin Legg]

Hi and welcome to the forum.


The good news is there's a very quick and easy way to delete the blank cells in your data. If you select the data and then press F5 to open the Go To window, click on the 'Special' button on the bottom left corner, choose 'Blanks' and click on OK, it will reduce your selection to only the blank cells. Then just right click > delete (or use the shortcut key CTRL+-), choose 'Shift Cells Left' and click OK. Once you've got the hang of the process it'll only take you a couple of seconds to do!

 

[Faseeh]

Hi stephleroux,


Welcome to the forums,


With your data in A1:G3 like below:

[pre]

A     B    C    D    E    F    G
Q1    1    2    3    4
Q2         1    2    3    4    5
Q3                   1    2[/pre]
You can also try this formula:


=IFERROR(INDEX($B1:$K1,0,SMALL(IF(ISBLANK($B1:$K1)=FALSE,COLUMN($A1:$J1)),COLUMN(A1))),"")

Press Ctrl+Shift+Enter, Drag to right and down.


Regards,