Merge and center blank space

[Sa_Ge]

Hello Uipath community
Is there a way to merge it like this dynamically?
test.xlsx (10.6 KB)
Thanks a lot

[supermanPunch (Arpan)]

Hi @Sa_Ge ,

Could we get some more details on the Process, for instance, Is it ​always you need to Find the First Column and Merge all the Cells Vertically up until the total ?

Also, Could you Check on the Below Package that helps us to perform Merging of Cells :

BalaReva Excel Activities - RPA Component | UiPath Marketplace

[Sa_Ge]

yes it will be first and second column merge blank cell vertically until it reaches cell with value and repeat to the end. thanks for the reply.

[supermanPunch (Arpan)]

Hi @Sa_Ge ,

For Cases like these, we would need to identify the pattern that is used and then we should be able to come up with a logic, to develop a Solution.

In this Case, I have identified the Pattern in the below way :

1. If There are Empty Cells after each Non Empty Cell (Vertically), All these Empty Cells are to be Merged Vertically and made into a Single Cell with the Non Empty Cell Value that was found first.
2. If there is a Total row, Need to Merge the Total row Horizontally across 3 Columns.

Taking into Consideration above points, I have developed a Workflow, that is Supposed to work dynamically in cases where there is Merging to be done with one or More Cells Vertically in Specified Columns. But again this is done specific to your case.

I have used an additional Workflow to get the Excel Column Name from the Index of the Datatable Column. Since, we do need to know the Cells in the Excel that have to be merged.

I have used the package Balareva.Excel.Activities, without which I couldn’t have performed the Merging Operation. Although there might be a way to do it using Invoke Code c#. The Cell Range that is to be Merged need to be provided in the Activity Merge Cells.

There are some Linq expressions that I have used to get the Non Empty cells Count, to get the Index values. I have used Skip and TakeWhile as well which made the work easier.

Check below the workflow and Test with your Data :
Merge Cells With Spaces.zip (15.4 KB)

I have also modified Activity Names in order to make the workflow understandable.

Let us know if this is not working or if you need an Alternate Solution.

[Sa_Ge]

It worked but i don’t quite understand the process sorry for my naivety may i have just for one column i’m truly grateful to the solution provided sorry that im inexperienced both programming and uipath Thanks alot
test 2.xlsx (9.2 KB)

[supermanPunch (Arpan)]

@Sa_Ge

may i have just for one column

@Sa_Ge , Do you need the Merging to be done only for one Column ? If so, Which Column ?

[Sa_Ge]

second column or Name in test2 excel sir thanks

[supermanPunch (Arpan)]

@Sa_Ge ,There is No Column named as "Name".

Are you talking about the Highlighted Second Column in the Below Image ?

[Sa_Ge]

Yes sir but the Name Column is in Test 2(new test file that i uploaded) which is just second column with Column Name i’m so
sorry
test 2.xlsx (9.2 KB)

[supermanPunch (Arpan)]

Hi @Sa_Ge ,

Please Note the following :

The Workflows Shown in the Below Image are used to get the Column Name in Excel i.e A, B, C ....

I have passed the Column 1 , Column 2 and Column 3 Indices in the 3 Workflows as shown in the Below Image to get their respective Excel Column Names.

In the Assign Activity of the Dictionary, I have passed the column Index and the Column Names of the Columns that are to be Merged based on the Spaces Condition. In the Image below as you can see I have passed both the Column Names, If you require only the Second Column to Perform the Merging Operation, then you can remove the first Column Name in the Dictionary.

Also, In the Input data Excel provided earlier, there was a Total row that need to be merged horizontally as well spanning 3 Columns. That is the Reason, I have also retrieved the 3rd Column Name in Excel and Passed it To Merge Cells Horizontally in the Total Row.

If the Total Row Merge is Not Required to Be done, we can Comment it or Provided a Boolean value to Trigger whenever required. For Now, I have Commented it.

Check the Updated Workflow Below. To Understand further the workflow, You can use Debug/Write Line to Print the Value at each Activity and analyse.
Merge Cells With Spaces.zip (14.3 KB)

[Sa_Ge]

Thanks alot for the solution i’ve alot to learn about Linq sorry for not understanding fast enough Thanks