Filter with blank cells

[srizki]

Hi all,
I have a spreadsheet containing test cases, where each test consists of multiple steps. The spreadsheet has a column (Column S) that contains the names of individuals assigned to each test. I want to filter the spreadsheet so that only my name and my assigned test steps are visible. When i attempt to filter the spreadsheet by my name and blank cells, the filter function displays my name along with blank cells for everyone else, rather than only my assigned test steps.
How do I solve this? Please advise.

 

[Joe4]

Please show us a sample of your data, the filter you are using, and your expected results (be sure to remove any sensitive data first).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[srizki]

Please show us a sample of your data, the filter you are using, and your expected results (be sure to remove any sensitive data first).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Joe,
thank you for your reply, the data is on my work computer so I cannot use work computer for Mr. Excel. But basically in column, S there is my name and under there two or three blank cells and somebody’s name then two or three blank cells, and then my name or somebody else name and then blank cells for the rows that has the steps.
I will try to send a sample, so you can see what is on other columns and rosso you can see what is on other columns and rows.

 

[Joe4]

OK, so I think I understand now.
So if you had something like the following:

Are you saying that after the filter, you want to see row 2-4 (your name and the two blank rows under your name)?

If so, then in order to do it with filters, I think you will need to populate those blank cells in column S with the value from above (not hard to do).
Otherwise, I think what you want to do will require VBA, as there is no easy way to distinguish the blank cells under your name from the other blank cells.

 

[srizki]

Joe,
thank you for your reply, the data is on my work computer so I cannot use work computer for Mr. Excel. But basically in column, S there is my name and under there two or three blank cells and somebody’s name then two or three blank cells, and then my name or somebody else name and then blank cells for the rows that has the steps.
I will try to send a sample, so you can see what is on other columns and rosso you can see what is on other columns and rows.

Just to give you an example, let's say my name is in in S45, column P has test steps numbers, so P45 has 1, P46 2, P47 3, P48 4, then S49 has someones name, P repeats numbers, (starting from 1 again. Column A, has Test case number, correspon ing to the name in column S, and then blank cells. Until then next TC number.

 

[Joe4]

Just to give you an example, let's say my name is in in S45, column P has test steps numbers, so P45 has 1, P46 2, P47 3, P48 4, then S49 has someones name, P repeats numbers, (starting from 1 again. Column A, has Test case number, correspon ing to the name in column S, and then blank cells. Until then next TC number.

See my previous post.

 

[srizki]

OK, so I think I understand now.
So if you had something like the following:

View attachment 109894

Are you saying that after the filter, you want to see row 2-4 (your name and the two blank rows under your name)?

If so, then in order to do it with filters, I think you will need to populate those blank cells in column S with the value from above (not hard to do).
Otherwise, I think what you want to do will require VBA, as there is no easy way to distinguish the blank cells under your name from the other blank cells.

Joe,
your first option says that filter, blank cell with the value from above, but if I filter my name and blank cell in the same filter, it filters my name, but all the blank cells not just under my name. That simply means it hides the rows where P does not contain my name.
I think your second option will work, but I cannot use VBA here. but I really appreciate your looking into this.

 

[Joe4]

If you cannot use VBA, then I think you will need to turn this:

into this:

That can be done rather easily, as described here: Fill in blanks in Excel with value above/below, fill empty cells with 0

 

[srizki]

Thank you Joe4, i use it all times, i will do this i guess, i was thinking there may be a new filter feature that might filter this or a formula something like, =AND(S2:S500, “Sohail”, ….

 

[Joe4]

The issue is that I believe that the Filters only consider the criteria for the particular rows being evaluated, and don't really take into effect where they reside and the criteria of rows around them.
The only way I can think of to do what you want without updating the values in the column would be to use VBA.