fill in the missing cells

Question

Hello, is there a way to auto convert from Table1 to Table2 below? &nbsp;&nbsp;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:&nbsp;toptop,sub1top,sub1,sub11top,sub1,sub11,sub12top,sub1,sub11,sub12,end12top,sub1,sub11,sub13…etc&nbsp;Thanks&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table 1top&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sub1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sub11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sub12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end12&nbsp;&nbsp;&nbsp;sub13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end13&nbsp;sub2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sub21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end21&nbsp;&nbsp;sub3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table2&nbsp;&nbsp;&nbsp;&nbsp;top&nbsp;&nbsp;&nbsp;&nbsp;topsub1&nbsp;&nbsp;&nbsp;topsub1sub11&nbsp;&nbsp;topsub1sub11sub12&nbsp;topsub1sub11sub12end12topsub1sub11sub13&nbsp;topsub1sub11sub13end13topsub2&nbsp;&nbsp;&nbsp;topsub2sub21&nbsp;&nbsp;topsub2sub21end21&nbsp;topsub3&nbsp;&nbsp;&nbsp;topsub3end3&nbsp;&nbsp;

jkpieterse · Answer

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.

hassan null · Answer

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

jkpieterse · Answer

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

hassan null · Answer

Yes I did but still not working.When I changed the cells format from General to Text, I get =B1 inside the empty cells.

jkpieterse · Answer

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).

Forum Discussion

fill in the missing cells

9 Replies

Resources

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