Greeting everyone,
I've tried to create a work flow to generate a complicated spreadsheet by using existing data. However, I couldn't work out the logic and struggle to use the right method to solve this issue.
I have a original data.xlsx
I need to generate a spreadsheet as template.xlsx
Thus, I need to create new columns of "Bus#", "kV", "Load ID", "District" and "each year's % of CityA Total MW" in original data. For Load ID, I need to leave those Subs without IDs as blank.
For each year's MW value, I need to import the values from original data spreadsheet and also divide each value by 1000. For Sub 4, 5, 6 and 7, I need to add their MW value with Sub 3 until 2021. After 2021, I only need to add Sub 4, 5 and 7 with Sub 3's MW value, Sub 6 leaves it as its own.
For each year's percentage value, I need to calculate a Total MW value of all the subs first for each year (create a new row to generate this value) and then use each sub's MW value divide by Total MW value to get percentage value.
Any suggestions or advise that I could create the work flow? Thanks for the help in advance.
