I will try to be as descriptive as I can in what I am trying to do, bear with me, it’s quite long.
I have two Google Worksheets – one is called “Source” and the other is called “Work“.
In the “Work” sheet, there are two tabs, one is called “Project” and the other is called “Countries“.
I am using a =IMPORTRANGE function to get a list of countries from the “Source” sheet into the “Work” sheet, “Countries” tab.
There are multiple lists that are getting imported from the “Source” sheet, so I am using multiple =IMPORTRANGE functions. Let’s call these lists “ListA”, “ListB” and “ListC” ect..
The reason I am using =IMPORTRANGE here, is because this list is dynamic and will change monthly, which gets updated in the “Source” sheet.
In the “Project” tab, I have numerous checkboxes. Let’s call them “Project A”, “Project B” and “Project C” ect.. When I click on the checkbox, the value of it changes from “FALSE” to “TRUE”.
My goal:
In the “Project” sheet, I have a company, called “Company 1” in the E column. In the E column, below, I click the checkboxes that correspond to “Project B” and “Project E”. I then am trying to achieve, that in the “Countries” tab, E column, below the same “Company 1” I would get a single list of countries, that are listed in “ListB” and “ListE”, alphabetically, and without any duplicates or spaces.
Here is the example sheet – https://docs.google.com/spreadsheets/d/12A3U9W1xoktWbMNe2znZnuPPfgUlLXk5kQR29Z3Pj2k/edit?usp=sharing
any place that formulas are, I have highlighted with a black border.
I will place how I would like the result to look in the “What I Want” sheet.
I want it so that when I click the checkboxes for whatever projects in the “Project” tab, the corresponding list of countries would appear below the company name in the “Countries” tab, for that list to be alphabetically sorted and for it to only display unique countries.
I tried using the =UNIQUE and =UNIQUE(FLATTEN functions but I kept getting spaces between the lists, and the text was not sorted alphabetically anyways.
Arrk is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.