Turning header/footer rows into columns

Question

Hello, I have a report which contains a header row which designates the start of the activity for a particular GL account ("10610 - Wells Fargo - Operating Account (Balance Forward As of 01/01/2023)" and a  footer row designating the end of the activity ("Totals for 10610..."). There are multiple accounts within the report so I would like to show the account number (10610) as a column to be repeated for each row of data for the respective account.  There are three separate accounts within the report. I'm attaching the source file and a screenshot of what I would like the end result to look like.

Thanks in advance for any assistance!

GL Data.xlsx

End result.PNG

davidskaife · Answer

Hi @wpennington

Here is a way of doing it:

I started the import on line 5 so it skips the first couple of rows as i didn't think you needed them? i then filter out any other null rows, create the Account No field/remove the opening/closing info from Doc Dt field. then using the Multi-Row Formula tool fill in the blanks for Account No, and finally move that field to the start.

I've left the opening/closing rows in as i assumed you'd still like them!

Header & Footer to Columns.yxmd