Filter on Merged Cells Not Working Properly - Help Please

[rustochango]

Hi forum,

Having an issue I've never run into before.

I've applied filters to my top row. Many of the cells in each column are merged. When I select a column to filter, and select the value I want filtered, it only gives me the top row instead of all the rows that the merged cell in the column encompasses. I'm trying to upload a couple screenshots to show you what I mean but it keeps asking for URL instead of a path on my computer.

Thanks for the help. The only solution I've found so far is to add columns that aren't merged that reference the columns beside them which isn't going to work for me here. I've never had to do anything special to get this to work as I'm intending in the past. Not sure what the issue is here.

Any help is greatly appreciated.

Thanks,
Rusto

 

[Fluff]

The problem is the "Merged Cells", they are a complete pain & best avoided like the plague.

 

[rustochango]

The problem is the "Merged Cells", they are a complete pain & best avoided like the plague.

I can't do anything about that. I've used the same type of spreadsheet on previous jobs and was successful. I don't understand why it's not working as I intend now.

I really have no options to change this spreadsheet as the merged column rows act a bit like headers for the unmerged rows beside them. Since I can't upload screenshots, here's a really basic example of the issue below. The left column would be first 3 cells merged to identify that each of the 3 rows to the right of it belong to that person. The middle column shows each individual ticket so it must remain as unmerged rows. Let's say this is for safety tickets. I also have to identify which trade. So the right column would be merged every 3 rows as well. This way I can print off a matrix by trade, or I can filter the list by name. The spreadsheet is much much more detailed with tons of names/rows and tons of data/columns.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ticket 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Ticket 2[/TD]
[TD]Trade[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ticket 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ticket 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Ticket 2[/TD]
[TD]Trade[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ticket 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


As I mentioned before, I've never had this issue and have been running spreadsheets like this for years. There must be an easy fix that I'm not thinking of.

 

[Fluff]

I'm surprised that you have been able to do this successfully in the past, because the 2 secondary rows are blank & I would therefore expect them to get hidden by the filter.

 

[rustochango]

I'm surprised that you have been able to do this successfully in the past, because the 2 secondary rows are blank & I would therefore expect them to get hidden by the filter.

I wasn't able to do the merge with the table tool here. The cell above and below the cells that say "Name 1" and "Name 2" would be merged. Man I wish I could attach a screenshot. This is not very easy to explain fully.

On MY spreadsheet they ARE merged.

 

[Fluff]

When 2 or more cells are merged, only the first cell in that merged range has a value. All the others are treated as blank.

 

[Greg Truby]

The problem is the "Merged Cells", they are a complete pain & best avoided like the plague.

Amen. My term for merged cells is "demonic possession of a worksheet", but "complete pain" + "plague" works.

I can't do anything about that [the merged cells].

Why not? It would be a far superior solution. Just:

• unmerge them all
• then select them
• do a goto special | blanks
• type in =[uparrow] for the formula and confirm with Ctrl+Enter

and voilá, you can now filter (and do other stuff like rack and stack using a pivot table). Need them to "look" like headers? Just drop in a bit of conditional formatting so that repeated values don't show up. Use white font on white background (my second choice) or set the number format for the conditional formatting to ";;;" (my first choice).

I've used the same type of spreadsheet on previous jobs and was successful. I don't understand why it's not working as I intend now. ... As I mentioned before, I've never had this issue and have been running spreadsheets like this for years.

Unlikely. Excel ain't changed how it would handle the scenario you describe in at least twenty years. My best guess is that you thought your previous situation was like your current one. But what was happening was that your multi-row cells were either also merged in a similar manner or else no rows were merged and your "multi-row" cells had embedded line feeds (alt+enter) and wrap text was turned on.