Add column for the countries, then sort by that column.
Below are the instructions to extract the country from each address to an auxiliary column to make it possible to sort the address by country.
- the countries are known and wrote on a standard form
- the data from companies is in columns A,B and C of Sheet1.
- the number of companies is "small".
- Create a list of the countries, let say that the list is on Sheet2!A:A
- Add the following formula to D1 on Sheet1
- Fill down as necessary
- Sort columns A-D from Sheet1 by column D
Note: If the above solution makes your spreadsheet become to slow to recalculate, an alternative is to use Google Apps Script.
How it works
- SEARCH is used in an array formula to search for each of the countries in the list of countries. The result will be an array of errors and a number. The position of the number corresponds to the row number the country found.
- IFERROR is used to replace the error elements by blanks.
- IF and ROW are used to replace the number returned by SEARCH by the row number. This row number indicates the row of the country.
- QUERY is used to concatenate the elements of the array. It uses a space as element separator
- TRIM removes the spaces. The result is a string.
- VALUE converts the string to a number.
- OFFSET is used to get the country from the list of countries.