How to "merge" multiple rows in one column while keeping the rest of rows separate? Excel 2010

Question

I have several rows with different data. One of my columns is for names. How can I merge the cells with the same name, so there is only one cell with the name up front, and several rows with the different data behind it? I can merge the cells, but when I try to sort/filter by name, only one row of data shows up.

Currently, the spread sheet looks like this:

I want it to look like this:

Most importantly, I want the filters to work properly. So, if I filter by the data that is in the "merged" cell, the rest of the rows still show up. If I simply merge the first column cells, only the first row of data shows up after filtering. It's as if the other rows have a hidden blank cell at the front.

Hope this makes sense! Thanks.

Answer 1

Merging cells is not a good idea since it brings up problems with selecting and filtering operations, as you have discovered.

If you want to avoid the repeated appearance of one name, you could use conditional formatting to kick in when the current cell has the same content as the cell above.

In the following screenshot you can see in the formula bar that cell A3 has the text "Adam" but conditional formatting hides that text in the cell.

Sorting and filtering will still apply to all rows, regardless of the conditional format.

Edit after comments:

Steps to achieve the result in the screenshot:

• select A2 to A13 (or more, but start in A2 and make sure A2 is the active cell of the selection).
• create a new formatting rule using a formula
• enter the formula =A2=A1
• select a format. You can set the font color of the cell to the same color as the cell background, e.g. white font on white background. In later versions of Excel you can use the custom number format ;;; which will suppress any characters

The position of the active cell when adding or changing the formula is important, because the formula uses relative references. That means that the formula will adjust to the current cell position and will always compare the current cell with the cell above.

When A2 is the current cell, using A2 in the formula is like using "me". Loosely translated, the formula says "Am I the same as the cell one row above me?" Exactly that question will be asked on every cell that the rule applies to.

For cell A2, A2 will be compared to A1. For cell A3, it will be compared with A2. Both have the same content, so the conditional format applies.

For cell A6 (Ben) the comparison will be against A5 (Adam). Not the same, so no conditional format will be applied.