Hello,
I go into a directory and pull all the excel files which contain a specific sheet and it puts the data in the following format
Completion | Value |
Yes | £100 |
Yes | £50 |
No | £0 |
Completion | Value |
Yes | £150 |
Yes | £25 |
No | £0 |
Completion | Value |
Yes | £300 |
Yes | £5 |
No | £0 |
Completion | Value |
Yes | £125 |
Yes | £125 |
No | £0 |
I would like to be able to split the data every time it gets to the title column (Completion and Value) and then copy it over to a new column, so it looks like this:
Completion | Value | Completion | Value | Completion | Value | Completion | Value |
Yes | £100 | Yes | £150 | Yes | £300 | Yes | £125 |
Yes | £50 | Yes | £25 | Yes | £5 | Yes | £125 |
No | £0 | No | £0 | No | £0 | No | £0 |
I need to do it an undefined number of times (as some months there will be 4 data sets, the next it could be 40), so ideally the workflow should be scalable.
Please can someone help me to do this?
Thank you
Solved! Go to Solution.
Hi @thrnma ,
if the number of rows does not change, you could use the Make Column tool:
Let me know if it works for you.
Best,
Roland
Hadn't seen the MakeColumns tool before - don't spend enough time in the lab!
Good solution
Thank you for this. I used it to fit to my bigger data set that I am using and it split the data where I needed it to, but then it did not put the columns next to each other like in your workflow
Instead it put the first 66 rows, then made a new column and put the next 66 rows in row 67-132 and then the next 66 rows in row 133-195.
Do you know why it has done this?
Thank you for this. Is there a way that I can get it to automatically change the number of columns. In this example, I need 4 sets of columns, but do I have to manually change the number of columns in the make columns tool if I want to do it for, say, 15 columns or can I set it up to change automatically? Maybe based on how many times a word appears in a column (such as make as many columns as the word Value appears)?
On the second Multi Row formula tool make sure Group is ticked, then I think it will work.
@RolandSchubert — Good use of "Make Columns". 👍