automating with merged cells

[karthik1997]

I need to update this table of sorts every week, i have made 3 iterations for the same in the sheet attached. But in the main sheet i only have one table within which all modifications would be done. The process usualyy takes a few minutes is there any way to spped it up using either macros or anything else.
can someone please help.
Thanks.

https://www.mrexcel.com/forum/excel-questions/1100772-automating-merged-cells.html

 

[pecoflyer]

Merged cells is one ox Excel's worst features. First get rid of them

 

[pecoflyer]

Cross post without links at Mr Excel....

 

[karthik1997]

HI,
Ill need to keep them as i use this table for charts

 

[GraH - Guido]

Hi, if I understand correctly you are after some kind of consolidation solution? Many ways to do so.
As a golden rule, merged cells should be avoided any ways in Excel, certainly in raw data. As Pecoflyer now already and rightfully posted.
What kind of solution would you be willing to use? Power Query, Macro?

PS: even for charts, merged cells are not required.

 

[karthik1997]

Macros, if i get rid of the merged cells then that would be an issue in the charts PFA the chart.

 

[GraH - Guido]

Then you should have made this post in the macro section. If possible, move it to that forum.
For the chart issue, just remove your merged cells. Does that really change your chart?
You have shown 3 iterations, fine, though I'm guessing those are the result of your manual intervention and not the raw data lay-out, or is it? How is the new data arriving? That is required in order to propose ways of automation.

 

[karthik1997]

The data arrives in a separate sheet and only 2 cells are added to this table ; this isnt the raw data layout and only the 2 cells are updated on this every week

 

[Kamolga]

Hi,

I would just put the data in a table and get a pivot to put the data the way I want (a macro refresh the pivot table when activating the iteration sheet).
It is better to use a 'Table' as in data sheet because it extend automatically: whken you add a line at the end of it, that line will be integrated in the pivot (iteration sheet). You can then make your graph out of the data sheet. It is important when you start anything in Excel to think in terms of database and lists...so records without empty cells (nor merged) in a table. From there you can get anything done very quickly.
Note that the data in the data sheet is wrong (not exactly the one you provided), it is just for the demo.

 

[karthik1997]

This works, thanks