sorting merged cells

Microsoft Excel 2007
October 26, 2009 at 08:08:14
Specs: Windows XP Professional SP 2, 2Duo CPU E6550 @ 2.33GHz / 3.00GB RAM
I am working with a spreadsheet on which there is a list of departments who are participating in a certain program. Several departments team up for this program, so I have merged cells in column A for notes, column B for # of participants, column C for their "option" #, column F for their assigned project #, and a few more columns on some of them for other things. I have not merged cells in column D where each department is listed separately and there are several more columns that may or may not be merged. So, the result is a spreadsheet where:
row 2 is the header row, and then
rows 3 & 4 are group 1,
row 5 is group 2,
row 6 is group 3,
row 7 is group 4,
rows 8, 9, & 10 are group 5,
row 11 is group 6,
row 12 is group 7
row 13 is group 8
rows 14 & 15 are group 9
rows 16 & 17 are group 10
rows 18 & 19 are group 11
row 20 is group 12
rows 21, 22, & 23 are group 13
rows 24, 25, & 26 are group 14
row 27 is group 15
etc., etc., etc. . . .
I need to have the ability to sort the spreadsheet by different columns for different purposes. Is there a way to somehow make certain groups act as a single row in order to accomplish that? For example, is there a function or formula that would take rows 8, 9, & 10 and call them one row, so I could then sort by column C, which is merged across those rows? Or is there any way at all to accomplish this - any sort of work-around, if there's not a function for this?

See More: sorting merged cells

Report •

October 26, 2009 at 09:41:22

Handling merged cells is not easy, and AFAIK, none of Excel's functions specifically handles merged cells.

I think that the first cell in a merged range is used, but this means that you will not be able to sort rows when the cell used for the sort is merged with a cell above it.

As an approach, perhaps have a source sheet with no merged cells and for groups enter the data in the appropriate cell in the first row for that group and subsequent cells in the group are linked by a simple =
e.g., B4 contains =B3

This makes the source sheet filterable and sortable.

Then add a 'display' sheet that links to the source data and uses merged cells.

Writing custom sort and or filter macros in VBA code sounds like a fairly major task. Doable but complex.

If you have a source sheet with no merged cells which gets sorted etc. then a macro to create a display worksheet might work. The macro goes through the sorted source data and if a cell to be copied (source to display) is the same as the cell above, then instead of copying it, it merges the two cells on the display worksheet. You could also include formatting to the code, such as borders and horizontal centering.

I will be interested to hear if others have ideas for how this might be approached.


Report •

October 28, 2009 at 12:35:36
Thank you very much for your help.

I decided to go with a source sheet and a display sheet, as you suggested.

That seemed to be the simplest solution, and it works for what I need.

I was so intent on trying to figure out a formula that I never even thought of doing that!

I appreciate that you took the time to help me.

Report •

October 28, 2009 at 14:23:33
You're welcome.

Report •

Related Solutions

Ask Question