Forum Discussion

laurenwhelan's avatar
laurenwhelan
Copper Contributor
Jul 21, 2021

Multiple search and conditional formatting

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...
excel
Formulas and Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
Jul 22, 2021
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.
  • laurenwhelan's avatar
    laurenwhelan
    Copper Contributor
    Jul 26, 2021

    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

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jul 27, 2021
      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.
      • laurenwhelan's avatar
        laurenwhelan
        Copper Contributor
        Jul 29, 2021

        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.

Resources