Filter with merged cells maybe there is an alternative way

Hi everybody, as my account name, I am new here.

Straight to my problem

I am organizing an audit summary.
For each company, there are 10 rows, and the first few columns (say A, B,and C), they are merged for COMPANY NAME, LOCATION, NUMBER.
Then from column D, it starts revision records, which means 10 rows may or may not be filled completely. Some may be filled up to the 5th row, and some are only 2nd row.

So later when I am trying to filter by those who are in the 2nd revision, problem comes. Autofit doesn't work which is expected, but also the entire worksheet looks not nice.

What I would like to have is to filter on the revision column, but no mater how many rows are filled in, it will be nice if all 10 rows for the companies that satisfy the 2nd revision to show up as the entire 10 rows.

Any one could provide me with a good idea ?

big thanks

Hello Iamnew,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Iamnew,

I need more explanation - I'm not noticing a great difference in what you want to achieve from what you have

You can't do that, you need to repeat the data for each row. The nearest I can think of to get to the layout you want is to have the data in every row and then a Pivot table summarising it (with not values) - would look like the attached

For example, the original "project" column merges 7 row.
After the filter on Column E "result"
Is it possible to keep the "project" column with merged 7 rows but only eliminate the "x" "active" "fail" and projects that do not contain "pass"

Maybe:

Please Login or Register  to view this content.

In what ways does it not work? If you'll enumerate them, I may figure out another "work-around"

Is the sample truly representative?

Meanwhile try this:

Please Login or Register  to view this content.

Yes it is.

the difference is

1. there are more columns with various input types.
2. more rows standing for more companies.

but in the sample I have neat input, while in the real one, some text/symbol/garbage are put in the cells that belong to "date" or "rev." columns. it s vast and confidential, so cannot copy that here.

But, if you only want to see the records with "pass" in Column E, then the last routine which I added to post #13 will do that.

I put this code in a standard module which I inserted into your book and I saved your book as macro enabled:

Please Login or Register  to view this content.

*You need to change the red sheet names to what they should be and then just copy
and paste your actual data to the "Example sheet" (The button in D1 should not be
affected by this) and then just click on the button!

** But I'm not sure if this is what you want