how to hide rows based on the value of two columns

Hi Everyone,

This is my first post, just signed up! Hope everyone is doing well.

I am hoping someone can help me with this.
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file.
Column B has the client's ID number; it doesn't come in play for the filtering.

Here is the set up,

A B C D
Doc # ID # Last Name First Name
doc68 a1a1 Smith John
doc22 a1a1 Smith John
doc21 a1a1 Smith John
doc20 a1a1 Smith John

doc22 a2a2 Jones Ralph
doc21 a2a2 Jones Ralph
doc20 a2a2 Jones Ralph



How can this be filtered to remove all the rows, for the same client, if column A, has the value of doc68?

All that is needed is the clients that don't have doc68 on file.
As per the example above, I need to filter the spreadsheet to show only,

doc22 a2a2 Jones Ralph
doc21 a2a2 Jones Ralph
doc20 a2a2 Jones Ralph,

I know how to format for dupes.

I have tried Conditional Formatting and Advanced Filter, Auto Filter and Sort.
I have come sooooo close, but still don't get the desired result.
There are doc numbers greater than 68, which are causing havoc!!
I can provide a sample excel doc if needed.

Any help will be greatly appreciated.
Thank you in advance.

Sal.

Well, you do mean "hide rows" and not "Delete rows", right?

This is doable with a simple AutoFilter. Activate the Auto Filter, then click on A1 and select CUSTOM. Use this:

Show rows where:
Doc #
does not equal
doc68

Now, are you saying that if a client has ANY row with a doc68 in it, you want all the rows for that client hidden? That would require a macro, I believe. Is that what you're after?

Try out this macro on your sheet. It pops a MsgBox to warn you which items it is about to delete, write those down if necessary. If you find that bothersome, comment out that line with an apostrophe, or delete it altogether.

Please Login or Register  to view this content.

Hi Everyone,

Hope you are all having a great day!

Thanks to JB for the code he created for my question.

Could someone help me with modifying this for the attached doc?

The rows are not being deleted properly, I think the code is checking the entire doc. What needs to happen, is, check only the columns C and D, Last Name and First Name. It is possible that a client has multiple docs for the same account number.

Therefore, if the account number (row B) is being searched, the result is at the account level, and not the client level.

Also can another value (69 = PEFP YES) be added to search for.

I have created and attached a mock document.

I hope I explained this sufficiently, if not, please let me know and I will try to provide more information.

Here is the code JB was kind enough to provide.

Please Login or Register  to view this content.

This is unclear. This is why I hate dumbed down first questions...invariably it leads to problems.

What my macro is doing is looking for Doc 68 and then memorizing the ACCOUNT NUMBER (column B), then deleting all rows with that ACCOUNT NUMBER.

You now want it to look for for doc 68 (yes or no) and then memorize the first/last name and delete all rows with that NAME?

Maybe show me an BEFORE and AFTER version of the sheet you uploaded so you don't have to explain even more, I can just compare.

I am easily confused, as you can tell.

Hi Everyone,

Think I got it, changed "68 - PEFP NO" to just "PEFP".

Took my own advice! LOL

I thought that some funky, ancient, beyond my comprehension, secret code had to be entered to search for part of the characters in the cell.


Thanks JB, for all your time and effort, I can't thank you enough.

Enjoy the rest of the weekend!!!

Please Login or Register  to view this content.

Ok, good stuff, so we got it right the first time...just amending for a different doc name? Take care, glad to help.