fill in the missing cells

[Hassan null]

Hello, is there a way to auto convert from Table1 to Table2 below?   Table1 contain a sheet created for a tree map but I need to fill in the missing cells to produce the following CSV text format:

top

top,sub1

top,sub1,sub11

top,sub1,sub11,sub12

top,sub1,sub11,sub12,end12

top,sub1,sub11,sub13

…etc

Thanks

Table 1

top
	sub1
		sub11
			sub12
				end12
			sub13
				end13
	sub2
		sub21
			end21
	sub3
		end3
Table2
top
top	sub1
top	sub1	sub11
top	sub1	sub11	sub12
top	sub1	sub11	sub12	end12
top	sub1	sub11	sub13
top	sub1	sub11	sub13	end13
top	sub2
top	sub2	sub21
top	sub2	sub21	end21
top	sub3
top	sub3	end3

[#2]

Sure.
- Select all cells (press control+home, followed by control+shift+End)
- Press F5, click Special
- Select "Blanks", click OK
- Press the = sign on your keyboard, press the arrow-up key just once
- hit control+Enter
- Select all cells again
- control+C
- press Control+alt+V (paste special), choose Values.

[#3]

Thanks Jan but it does not seem to work? I keep getting zeros "0" in the empty cells.

[#4]

Did you press the up arrow key immediately after pressing the equal sign, but before pressing control+enter?

[#5]

Yes I did but still not working.

When I changed the cells format from General to Text, I get =B1 inside the empty cells.

[#6]

You should not change the cell format to text!
Also, make sure the active cell is NOT on row 1 (you can press the tab key until you have a cell on a different row).

[#7]

I'm sorry Jan this still does not work for me! Perhaps we have different versions of Excel or it cold be an environment configuration differences? I have MS 365 Pro Version 1708.

Can you please post the example table you were able to generate with these steps?

Thanks

[#8]

It might be better if you post an Excel file with anonymised data.

[#9]

Hi Jan, I can't upload the actual table which is is huge and also contain proprietary information. I'm attaching a smaller model for you to work with.

Thanks

[#10]

Aha. Key is that the cells in question are not blank. We'll use separate columns.

In cell K1 enter the formula =A1. Copy to the right as far as needed (to N1 in your example)

On cell A2, enter this formula: =IF(LEN(A2)>0,A2,K1)

Copy to column N and then copy row 2 down as far as there is data in your original table.