In this instalment of our Automate Excel series, we will look at how to merge your rows across different worksheets in Excel. Consider the following scenario: sales data is split across three different regions (North, South, and Midlands). We need to merge this data to create one combined National worksheet. To achieve this, we will use the Encodian Flowr ‘Merge Rows in Excel’ action.
Encodian Flowr‘s ‘Merge Rows in Excel‘ action will merge the rows from each worksheet contained within the files provided, producing a single file with a single worksheet. Optionally, you can target specific worksheets from each file.
To simply merge the worksheets but not the row data please review the ‘Merge Excel Files‘ action.
As with all of the Flowr Excel actions, the data in your Excel file doesn’t need to be in table format (it works with or without tables). Below is a screenshot of the Regional Sales Excel file:
NOTE: The accompanying video for this post details how to merge Excel rows when the ‘North’, ‘South’, and ‘Midlands’ worksheets are contained within separate files rather than a single file.
For this solution, I have created an instant flow, however, for a real scenario you may want to change this to be a scheduled flow. The flow is simple, with only 4 actions:
I am using OneDrive as my file source, so I am using the ‘Get file content‘ OneDrive action to get the file content from my Regional Sales Excel.
Next, we have the Flowr action.
You can decide what file format you want the action’s output to be in. For this solution, I am keeping it in Excel format.
You can decide the order in which you want to merge the data into the final document. In my Regional Sales Excel, the order of my tabs are North, South and Midlands, but in the new National Sales Excel, the data needs to be in North, Midlands and South order. This means I have ordered my tabs as 1, 3 and 2 in the Flowr action. The inputs are integer values, not the names of the worksheets, so the first worksheet in the original file will be number 1, the second will be number 2, etc.
The ‘Include First Row‘ input determines whether the first row will be merged alongside the data in the final document. When a new merge starts, the first line will be the header row instead of the first data line. ‘Preserve First Row‘ is different because it will preserve the header row only at the top of the new document.
If you are merging multiple files, you may need to be more dynamic about how you get the information needed for the Flowr input. If this is the case, you can enter the document information using an array variable rather than the UI inputs:
The array needs to be in this format:
[
{
"fileName": "fileName.ext",
"fileContent": fileContent,
"worksheetsToMerge": [
1,
2,
etc.
],
"includeFirstRow": true/false
}
]
The video will cover this scenario in more detail.
The last step in the flow is creating a new National Sales Excel file using the outputs of the Flowr action.
Let’s run the flow and look at the results!
We can see that all the data has now been added into one page. We still have the header row as we set the ‘Preserve First Row‘ input to ‘Yes‘. We can also see the order of the data has been merged correctly with the order of North, Midlands and South.
Watch Sophie’s companion video
Search 150+ Actions to see how Flowr can save you time
Sign up for your free 30-day trial; no cards, catches, or contracts.
No job is too big or too small for our Professional Services team!
Technical Evangelist