Sorting and Filtering with Empty Cells

[royca]

Hi Folks,

I have quite a large sheet formatted something like the below. Blank cells share common data with the populated cell above them, maybe not great formatting but it is what I get.

I would like to be able to sort or filter by location for example with the empty cells below South3 being included as if they were South3 (like South3(0), South3(1), South3(2)). Is there a way to do this without breaking into some VBA or filling in every cell? Any ideas would be great.

[TABLE="width: 500"]
<tbody>[TR]
[TD]SN[/TD]
[TD]Location[/TD]
[TD]Program[/TD]
[TD]Version[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]South1[/TD]
[TD]P1[/TD]
[TD]1.1[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]South2[/TD]
[TD]P2[/TD]
[TD]1.1[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]South3[/TD]
[TD]P1[/TD]
[TD]1.1[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P3[/TD]
[TD]2.1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P4[/TD]
[TD]2.1[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]South4[/TD]
[TD]P3[/TD]
[TD]2.2[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[JonXL]

Without using VBA I can only think of using "helper" columns.

Say SN is column A, insert two columns after B and in the new C2 enter this formula and fill it down:

=IF(A2="",A1,A2)

Follow the same process for the new D2 but referencing column B:

=IF(B2="",B1,B2)

You'll now filter using columns C and D.

 

[Peter_SSs]

I'm wondering if you are avoiding filling the empty cells simply because of the perceived difficulty of that?

If so, it is easy to fill the blanks with what is above them (without vba) as follows
1. Select the entire data range (or all of columns A:G)
2. Press F5 -> Special... -> Blanks -> OK
3. Now type an = sign, press the up arrow key, hold down the Ctrl key and press Enter
4. Select the entire data range (or all of columns A:G) again, Copy, PasteSpecial -> Values

Done

 

[JonXL]

That's amazing, Peter!

 

[royca]

That's a new one for me and pretty cool.

For this one though I was hoping for something that grouped the rows together.

 

[Peter_SSs]

For this one though I was hoping for something that grouped the rows together.

There is nothing that I know of that will necessarily keep those rows 'locked' together in the original order if you might be doing sorting and/or filtering on any column(s) in any order(s).

Depending on just what you have in your real data, about the best bet I think will be along the lines suggested bu Jon (though there are errors in the logic of Jon's formulas).
Here is my adjustment to those formulas and I would just place the new columns to the right of your original table to maintain its integrity.
The formula in H2 is copied across to I2 and down.
Note though that, depending on what sorting/filtering you do, those rows that should be together, could get separated.
If you may want to return to this original order, then to be safe I would also add the new column J, just numbered 1 to n so that you could always re-sort on that column to restore the original order.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	SN	Location	Program	Version	Data1	Data2	Data3	SN2	Location2	Original
2	111	South1	P1	1.1	10	15	No	111	South1	1
3	222	South2	P2	1.1	15	15	No	222	South2	2
4	333	South3	P1	1.1	10	15	Yes	333	South3	3
5			P3	2.1	15			333	South3	4
6			P4	2.1	20			333	South3	5
7	444	South4	P3	2.2	15	20	No	444	South4	6
Sort Filter Empty Cells (2)