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?

Hi, 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. Regards

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.

You're welcome.