Merged Cells in filters

[Nath]

HI

I have a spreadsheet to store 4 different pieces of data
realting to an employee. In column A there are the
employee names. The 4 rows of data for this employee are
in column B. I have the 4 rows in column A merged, so the
employees name is only shown once. However when i put on
Auto Filter, i only see the first row of data for an
employee selected.

Is there a way of selecting a name from the auto filter
and presenting all 4 rows of data.

TIA

Nath.

 

[Bernie Deitrick]

Nath,

Merged Cells = EVIL ;-)

To keep the evil (which I don't recommend) copy the column with the names,
and insert it as a new column B.

(The above step can be skipped to completely remove the merged cells, just
use the following instructions on column A rather than B.)

Select the merged cells in the new column B, select format, then on the
alignment tab UNcheck the merge cells box. Then use Edit | Go To...
Special and choose "Blanks" then press OK. Type an = sign, press the up
arrow key once, and press Ctrl-Enter. Then select column B, copy, and paste
special values.

Then use the merged-cell-free column as your filter, and all will be well.
And in the future, remember to not use merged cells in databases. For
formatting reports, they are less evil, but they do not belong on data
sheets.

HTH,
Bernie
MS Excel MVP

 

[Nath]

HI

I have done all that you said up to the bit where you type
an = sign, i did this and nothing happened. Should it?

Nath

 

[Bernie Deitrick]

Nath,

Let's say that you have a merged cell in A1:A4, and A5:A8. When you select
those cells, and unmerge them, A1 gets the value from the first merged
group, and A5 gets the value from the second group. When you select all the
blanks cells, you will end up selecting A2:A4, and A6:A8. When you type =,
press the up arrow key once, then press Ctrl-Enter, cell A2 gets the formula
=A1, cell A3 gets =A2 (which then refers to A1), etc. so that the end
result is that cells A2:A4 get references to cell A1, and A6:A8 get
references to cell A5, filling them in with the values that were orginally
in the merged cells.

If nothing happens, you may have not selected the blank cells correctly, or
you had the wrong selection when you selected the blank cells, or....

Give it another try and follow my instructions exactly.

HTH,
Bernie
MS Excel MVP