Data stacking (trail balance)

Hi,

I am trying to prepare workflow which will consolidate all monthly trial balance numbers one after another:

Current format:

Please refer attached excel file (problem)

Solution:

on first column we need All account type and then all month's numbers should be place on after another (every month number should be "Debt - credit")

Format should look like this:

Would it be possible to automate this exercise through Alteryx, if we can;t automate this entirely then would it possible to create separate tabs for each month, I mean in sheet1 we should have First three column then in sheet 2 we have next three column and so on. In this way, I can create a workflow which will give me the desire result.

Thanks in advance for your help.

Thanks,

Satya

Problem file.xlsx

Preparation

Accepted answers

CharlieS

@spsanu_EY

I completely agree with @Philip, there's definitely opportunity for a better input state. It took a bit, but I've attached the "gnarly" solution.

20180813-CreditDebitTransform.yxmd

All comments

Philip

Is this the way you receive the data each month? Personally, I'd work out a way to get the data in a better format for easier processing. You're going to have issues because of the two-row header and the addition of new columns instead of new rows.

If you're determined to make this work, I can create a workflow. It will be gnarly, it might be easily broken and I'd never guarantee the results (based on processing spreadsheets like this in the past), but I can create it. Just let me know.

Better would be to ask for a data source with columns of date, account, debit, credit. It will make your workflow a lot simpler, easier to maintain, and give you dependable outputs.

CharlieS

@spsanu_EY

I completely agree with @Philip, there's definitely opportunity for a better input state. It took a bit, but I've attached the "gnarly" solution.

20180813-CreditDebitTransform.yxmd

spsanu_EY

Hi Philip,

Thanks for taking out time and looking into this problem.

Regarding the input file, it is not in our hand to get date in right format. We can only push our clients to sent data in excel format, rest we need to play around with Alteryx or excel to get the desire output.

However, normally we receive trial balance in proper format format (separate tab for each month), this was one of the case and I am guessing we are going to receive more files like this, hence I was looking for proper solution through Alteryx.

Thanks & Regards,

Satya Prakash

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335