Impossible sorting excel
Solved
Hello,
I cannot sort a spreadsheet in Excel because I have merged cells in certain rows and in different columns. The message is:
"this operation requires merged cells to be of the same size"
How can I avoid having to retype my entire table, which is quite large?
And without having to change this formatting?
Thank you in advance
I cannot sort a spreadsheet in Excel because I have merged cells in certain rows and in different columns. The message is:
"this operation requires merged cells to be of the same size"
How can I avoid having to retype my entire table, which is quite large?
And without having to change this formatting?
Thank you in advance
Configuration: Windows XP Firefox 3.5.6
4 answers
-
Hello everyone.
As for me, I arrived at this topic because I had the same problem, but I just found a solution.
To explain the context: I have a file to send with tables on the left and charts on the right. The table that was at the bottom of the page needed to have the same width as the ones above it, even though it had fewer columns. So, I had to merge some cells. For this table that changes every week, I added a button linked to a macro that sorts the data in descending order when clicked.
To successfully perform this sort despite the merged cells, I used a simple but effective trick:
I created another table that contains the values without merging.
The merged table simply points to the values of the other table.
For example: column A: unmerged title but I entered the formula: "=J5"
Columns B and C are merged but I entered the formula: "=K5"
Columns D and E are merged but I entered the formula: "=L5"
The sort is performed on the J;K;L table, and the display updates on the A;B;C;D;E table.
The J;K;L table is hidden behind one of the charts, but in your files, it could also be set to white font on a white background with no border to make it invisible.
Hoping to have helped you.
