Multiple search and conditional formatting

[laurenwhelan]

I have a weird one:

- I have a spreadsheet with patient names, addresses etc.

- I need to filter out all postcodes. I have a list of post codes for post codes within an "allowed" area and ones that are out of area.

- I am manually searching and then using conditional formatting to colour yellow and then once I have searched the entire list of post codes within the sheet, I can use the column sorting to hide all conditional formatting cells to show all remaining addresses that were not in the post code list.

I did try an IF statement to put the many post codes in there and the  a sort and then a count but it didn't work.

Catch is, the post codes that are part of the patient addresses are in the same cells therefore cannot to sorted into any order as it would sort the street number instead of the post code. 

There is a total of 84 post codes that are "allowed" in a list of a few thousand patients.

I tried so highlight the allowed post codes in their column and then name it so i could reference it in an equation but it didn't work.

Maybe I should do a Macro and then perform all the searches with conditional formatting but how do I save the macro so that it will work on any spreadsheet when I do a databse extraction for this sort of stuff?

[#2]

Can you perhaps show us an Excel file with some anonymized sample data? Just a couple of rows will suffice, as long as it contains both "valid" and "invalid" codes. All other columns can be cleared. I assume you have a list of "Valid" post codes somewhere, make sure you include that list (but convert it to fake codes) Here too we only need a couple of codes.
Important: make sure your sample file only contains made up addresses and no names of people.

[#3]

@JKPieterse 

Hi
Thanks for the message.
While I was waiting for a reply, I ended up doing a Find for all postcodes that were within the numbers I needed and then highlighted them in yellow. Then I Chose to Hide all the rows that had no fill. It was a long way of doing it as there are 83 post codes that I needed to filter for Invalid/out of area post codes.

I have attached some screen shots. Unfortunately, the address column has the street number and name in it and cannot be separated otherwise it would be a sinch! I have replaced the street name and number with "123 None Street" so you can see the formatting etc...

Thanks for your help, I really appreciate it

[#4]

Looks as if the post codes are always the last four characters in the cell? Anyway, if you ask an Excel question, why not attach an Excel file, not a screen-shot.

[#5]

Yes that is right and Because I had to redact a lot of information..... just to get the screenshot while keeping the quality of the data there

[#6]

@JKPieterse 

If you prefer for a spreadsheet then I can..... this has very limited data and I don't know if it will impact anything...

I didn't realise a screenshot would be an issue.

[#7]

@laurenwhelan Looks like all your addresses have the post code as the last four characters. In that case, there's a relatively simple formula, see attached. I added an extra column on the right-hand side of the table and I converted your post code list to a table as well (tblPostCodes).