Forum Discussion

Deleted's avatar
Deleted
Oct 17, 2017

Help with formula to copy headings

I have an export from my accounting software for all my transactions for the year. Each account will have a heading with the unique account number and then all the accounts will then show below. I want a formula that will copy the heading to a column in each row. I don't want to do it manually as it is thousands of lines.

 

 

 

Formulas & Functions
tips and tricks

4 Replies

  • Deleted's avatar
    Deleted
    Oct 17, 2017

    Thanks for the replies. The formulas did exactly what I wanted.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 17, 2017

    Hi Minette,

     

    If the data structured like this

    the formula statrting from D2 could be

    =IF(ISNUMBER(A2),LOOKUP(9E+99,1/ISTEXT($A$2:$A2),$A$2:$A2),"")

    as in attached

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Oct 17, 2017

    Hi Deleted,

     

    I'd recommend using Power Query for automating this.   It's built in to Excel 2016 and is a free download for Excel 2013 / 2010.

     

    It's perfect for pulling in and cleaning up exports from accounting systems.

     

    If you can upload a small sample of the  export file I can demo a solution for you.

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Oct 17, 2017
      Or you could go with this formula, assuming your line you want to copy down always has a "-" hyphen in it.

      =IF( ISNUMBER( SEARCH("-",A2) ), A2, D1)

      So if your formula goes in D2 to start then It looks for the "-" in cell A2 and brings that value back. Otherwise it just brings back the value in the cell above (which will be the account name from the cell above)

      So assume

Resources