Suggestions on how process an unsightly excel document...

Question

Hi everyone

I have a need to get our statutory accounts into Alteryx to create an output csv file with the columns you can see here.  The first line will always start with TOTREVEN

And then there could be none, one or many columns in between the final column that i need the values from.  Ideally, I'd be able to take many multiple files and process in the same way, but really not sure how to deal with all the white space...

I wondered if anyone had any suggestions?

Thanks

Cass

Trial Balance.xlsx

TonyA · Accepted Answer

The attached should get you most of the way there. You have a few random columns that have temporary names but I didn't want to throw away any data. I also kept all the header rows in a separate data set in case any of that data is needed later.

ck2024 · Accepted Answer

Hi Tony

Thanks very much for your help - your solution gave me the inspiration I needed.

I ddin't want to use skip rows as the number of rows could vary and I wanted to make sure that I could deal with that without having to amend the workflow each time so I used suggestion of transposing to do that first and then filter and append and then filter to remove the unwanted strands out.

Thanks a lot for your help!

Cass