Forum Discussion

Hassan null's avatar
Hassan null
Copper Contributor
Jul 10, 2018

fill in the missing cells

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:  ...
excel
Formulas and Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
Jul 16, 2018
It might be better if you post an Excel file with anonymised data.
Hassan null's avatar
Hassan null
Copper Contributor
Jul 17, 2018

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

 

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jul 18, 2018

    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.