Filter all but one middle row

[kate61487]

I have a list which I would like to be able to filter while leaving one merged row/cell in the center always visible. Is this possible?

Essentially I have a data set sorted by date. I have reports of an issue prior to a corporate communication going out and then after. I would like to be able to filter the list by location (one state or several) while leaving the merged row/cell visible so that I can tell, at a glance, which reports/how many reports were received prior to the communication and then after. I've put together an example, but can't get to pic hosting websites at work and apparently can't paste screenshots or attach images directly so I've recreated as best I can below:


Raw Data:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]Record Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Date[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl65, width: 314"]Address[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]2016-01[/TD]
[TD]11/1/16[/TD]
[TD]504 Piney Pl[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]2017-01[/TD]
[TD]2/3/17[/TD]
[TD]51 Star Cliff Ct. [/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]--------[/TD]
[TD]merged[/TD]
[TD]row (communication issued)[/TD]
[TD]----- [/TD]
[/TR]
[TR]
[TD]2017-02[/TD]
[TD]5/27/17[/TD]
[TD]765 Halyard Ln.[/TD]
[TD]CA[/TD]
[/TR]
</tbody>[/TABLE]


What I would like to display when filtering for CA:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]Record Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Date[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl65, width: 314"]Address[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]2016-01[/TD]
[TD]11/1/16[/TD]
[TD]504 Piney Pl[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]----------[/TD]
[TD]merged[/TD]
[TD]row (communication issued)[/TD]
[TD]-----[/TD]
[/TR]
[TR]
[TD]2017-02[/TD]
[TD]5/27/17[/TD]
[TD]765 Halyard Ln.[/TD]
[TD]CA[/TD]
[/TR]
</tbody>[/TABLE]


What is actually displayed when filtering for CA:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]Record Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Date[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl65, width: 314"]Address[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]2016-01[/TD]
[TD]11/1/16[/TD]
[TD]504 Piney Pl[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]2017-02[/TD]
[TD]5/27/17[/TD]
[TD]765 Halyard Ln.[/TD]
[TD]CA[/TD]
[/TR]
</tbody>[/TABLE]


I appreciate any suggestions!

 

[JLGWhiz]

Since the filter operates on the principal of hiding all rows in the filter range except those which meet the filter criteria, your Merge Row would need to have the filter critera entered in the filter range to remain visible.

 

[kate61487]

so no way to exclude a row or lock a row or tell the filter the range is A1:D3 and A5:D5 but not A4:D4? (I didn't really figure there was, but you never know the work arounds some people have figured out)

 

[Fluff]

As long as D4 is blank is blank you could filter on CA and Blanks

 

[kate61487]

Thanks, Fluff. The only downside to doing state + blanks is with a full listing of states it's much easier to type in what you're trying to filter to than to scroll and there's no way (to my knowledge) to type the filter and select blanks on one go (would have to finish the filter then go back in to add blanks).

I may un-merge the row and type in all possible values in the fields most likely to be the columns filtered, then format text to make it invisible so that a typed filter would always include that row too. it will look a little messy on the filter drop down, but may be my easiest solution.

 

[Fluff]

In the filter select Text filter > Custom Filter > equals > CA > or > equals > ok