Merge Multiple Sheets in single sheets with preserving format and colors

Hi everyone ,

My Requirement is i have an excel with different sheets i have to merge it to single sheets with formats and colors preserved!!

Main input excel with different sheets

Individual sheets
15 years

10 years

5 years

Final output required

Highest to be at the top eg:15 years then 10 & 5 with one line space

any suggestions @ppr @Yoichi @Palaniyappan ?
Thanks in advance


Have a view on this thread


Hi @kavya.s16 ,

From the Previous post mentioned :

The Point values where present in the Points column, which I assumed the value 10 Points, 15 Points are the same values added to the first row.

In this case, How do you get the 15 years, 10 years, 5 years values ?
We maybe able to sort it at the beginning stage and then perform the Merge and Colouring.

That 15,10 and 5 points are not constant , instead it comes before grouping

so first i group the datatable based on the column called YOS and get a list of of datatables

then for each and filter out some required columns and then merge and color them @supermanPunch

@kavya.s16 , Yes, We would like to know what is it’s source, If it was present in a column already, then we could keep the column for sorting purpose, and then after the sorting is done, we could remove the column.

This is an easier approach to tackle from the source itself.

Else it would be a little difficult to perform the sorting or additional steps would be involved for sorting.

1 Like

@supermanPunch basically there is a datatable of 97 columns and first i need to group them according to a column name YOS and then it results in a list of datatable ,then inside the for each i am sorting it and removing the uncessing columns and just keeping 2 and then merge and color are done only for those two columns

@kavya.s16 , If you are still facing a problem in sorting, Could you let us know How the 15, 10, 5 values are mapped to those table data ?
Maybe tell us what is this value and How you are assigning it to the First, Second and third datatable.

If we get to know where is this data coming from we should be able to sort it.

@supermanPunch no i am nothing facing the problem now ,fyi that 15,10 5 values come from main datable before the remove the unwanted columns

I just used dt1(0)(0) = where dt1 is item.defaultview(False,“YOS”) (item is from for each of list of datatables)

and the first , second and third datatable i am getting from this for each only and i am adding it to outputDT according to ur code

@kavya.s16 , Glad to know it was resolved.

1 Like