1

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:

enter image description here

I want it to look like this:

enter image description here

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.

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.

enter image description here

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.

  • Thanks, Teylyn. I think you have me on the right track, but I need a little more information about what the conditional formatting rule would be. How can I set the formatting to display only the first instance of a given value in the first column? I'm not sure how many "Adams" will be in my final spreadsheet, so it's tough to know how many rows I would have to apply the rule to. Also, it looks like this method would have me set a rule for each name? – Salcedo Oct 22 '15 at 20:58
  • The conditional formatting rule is showing in the screenshot. It is a rule with a formula and the formula is =A2=A1. You can apply the rule to the whole column. You don't need a rule for each name. In my screenshot I had only 13 rows of data, so I applied the rule to only 13 rows. If you use an Excel Table (Insert > Table), then existing conditional formatting will automatically be applied to new rows of data. – teylyn Oct 22 '15 at 21:12
  • I guess it's the format that confuses me. Do you have it set to white fill, or is there a way to set the formatting to display a blank cell? I guess I can set the formula or choose the "format only duplicate values" option from the conditional formatting menu, but I can't figure out what you're doing with the format box. – Salcedo Oct 22 '15 at 21:17
  • The formula is sort of working. Still not sure what to do with the format box. I set it up to apply a color and pattern to the cell so I can see the formatting, but it applies to the top instances of the given data, not the bottom. So, using your example from above, I see 3 colored/patterned Adam cells, and one normally formatted Adam cell in row 5. How do I get the top Adam to be the normal one? And how do I get the conditional formatting to show a blank cell for subsequent Adams, etc.? – Salcedo Oct 22 '15 at 21:51
  • I added some more explanation. The concept of relative references can be challenging. – teylyn Oct 23 '15 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.