Forum Discussion
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 ChanIron 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.