Merged Cells and Filters

[scotthannaford1973]

Hi all

any suggestions appreciated here...

Okay - to keep it simple, what I effectively have is a spread sheet with two columns:

(A) - the first column is made up of merged cells, three cells in each (i.e. A1 is made up of A1, A2, A3; A4 is made up of A$, A5, A6 etc)
(B) - this column has standard unmerged cells

Column A has the merged cells with content that I want to filter, and Column B has content - and I want all three cells of column B to appear when I filter column A.

Generally, when you filter a merged cell in Excel (i.e. A1, A2, A3), the results only show the cell that had the content, so I might see A2 and not A1 and A3.

What I would like is to be able to apply a filter that either shows all rows that form the merged cell, or be able to specify "display filtered row plus two rows above" or something similar.

cheers!

 

[Fluff]

Best advice is to remove the merged cells & fill the now empty cells with the value

 

[scotthannaford1973]

Best advice is to remove the merged cells & fill the now empty cells with the value

thanks, Mr Fluff; not a fan of merged cells, but unfortunately the user will want to keep them in...

 

[Fluff]

In that case you will either need a helper column like

Excel 2013/2016
	A	B
1	fluff	fluff
2		fluff
3		fluff
4		fluff
5	next	next
6		next
7		next
Quote

Cell Formulas
Range	Formula
B1	=A1
B2	=IF(A2="",B1,A2)

Where A1:A4 & A5:A7 are merged

Or you'll need VBA, but VBA hates merged cells