Forum Discussion

Sayen Mulu's avatar
Sayen Mulu
Copper Contributor
Jul 10, 2018

Filtering Data By Location! Formula that works in spreadsheet not working in Excel

I am trying to filter emails using location. Unfortunately, my data only have city & state names which makes filtering even more difficult. So i have gathered all the cities of Countries that i wanted filtered. I have been given a formula for spreadsheet that i wanted to use but was limited by cell numbers. I am trying to filter using formula to get desired emails from desired cities. 

Formula used for filtering cities in spreadsheet 

 

I3 - =FILTER(A3:G, RegexMatch(E3:E,Sheet1!A1)) Including (Australia, UK, US and CAD cities)

Q3 - =FILTER(A3:G, RegexMatch(E3:E,Sheet2!A1)) Including only (US and CAD cities)

X3 - =Filter(A3:G, NOT(RegexMatch(E3:E,Sheet1!A1)))  Evrey cities thats not from AUS, UK, US AND CAD...)

 

I have tried exporting in xlsx file format. And it shows error on the formula message. I was just wondering if there is any formula for this on excel. 

For the spreadsheet  you can view the sample here. Thanks https://docs.google.com/spreadsheets/d/1Ol5MpaiB4aeJlfOH0S-gprEV8wpd6aDud9hepjEq7TU/edit?usp=sharing

 

1 Reply

  • Man Fai Chan's avatar
    Man Fai Chan
    Iron Contributor

    I think you have to break down the column LOCATION into two/three columns named: CITY / REGION / COUNTRY. And then, use the filter function in excel so that you can copy the result to other worksheet. 

     

    If it is impossible to break down the column LOCATION, I suspect it requires VBA to complete the task. 

     

Resources