Background
I've some data to overwrite into a range in a formatted excel sheet. As I would like to have string and double data in the same columns in excel, I've split the data in to components to output into the formated excel sheet.
ID | F1 | F2 | F3 | F4 | F5 | Latest Quarter |
1 | Sep 20 | Dec 20 | Mar 21 | Jun 21 | ||
2 | Expenses | Food | $2000 | $1900 | $1800 | $1700 |
3 | Water | $300 | $350 | $400 | $450 | |
4 | Gas | $500 | $600 | $700 | $800 |
Component 1
ID | F1 | F2 | F3 | F4 | F5 | Latest Quarter |
1 | Sep 20 | Dec 20 | Mar 21 | Jun 21 |
Component 2
ID | F1 | F2 | F3 | F4 | F5 | Latest Quarter |
2 | Expenses | Food | $2000 | $1900 | $1800 | $1700 |
3 | Water | $300 | $350 | $400 | $450 | |
4 | Gas | $500 | $600 | $700 | $800 |
In order to format F3 to Latest Quarter as double, I had to split it and also renamedthe Row Headers (F1 and F2 to Title & Title2) and Monthly Data (F3 onwards) separately.
Key Issue
As per the screenshot, the join tool somehow messes up the order
Left Input
ID | F3 | F4 | F5 | Latest Quarter |
2 | $2000 | $1900 | $1800 | $1700 |
3 | $300 | $350 | $400 | $450 |
4 | $500 | $600 | $700 | $800 |
Right Input
ID | Title | Title2 |
2 | Expenses | Food |
3 | Water | |
4 | Gas |
Joint Output
ID | Title | Title2 | F3 | Latest Quarter | F4 | F5 |
2 | Expenses | Food | $2000 | $1700 | $1900 | $1800 |
3 | Water | $300 | $450 | $350 | $400 | |
4 | Gas | $500 | $800 | $600 | $700 |
I was able to sort the order and bring the Title columns to the front but was not successful in keeping the latest quarter as the last column. The data were all correct just that the order of the columns was mixed up. Any help in achieving the above is appreciated.
Edit: Added the join tool config screenshot as well.
Solved! Go to Solution.
The Join tool has the same functionality of the select tool ... Why don't you reorder in the join tool?
Yea I've re-ordered it in the join tool (see screenshot), with Latest Quarter as the last column, but it doesn't seem to be working. The latest quarter continues to find its way in between other data columns instead of being at the end.
This happens because you are using the dynamic select tool before the join tool... The workflow just "forget" what field are selected because you are doing that dinamically. Try to use a Select tool instead dynamic select tool.
Perfect! Added the select tool just before both input anchors of the join tool and problem solved.