first time posting here!

I have the following scenario:

1. 1 Google Sheet with information sorted in tables (Master Data)
2. 1 Google Sheet that =Importrange the data from the Master Data Google Sheet.

I need to import one time, or multiple times, some of the rows that are Imported from the Master Data based on the following criteria: On the Master Data Google Sheet, a column would be present, showing in which Country/Countries the student lives. If the student lives 1 country, import the row once. If it is in 2,3,4... countries, import the same row it 2,3,4... times.

Right now, I am using the following formula:

=QUERY({IMPORTRANGE(Reference!A8,Reference!$A$2&Reference!B6)},"select Col6 where Col10='"&'Advanced Settings'!B5&"'")

This formula Imports from the Master Data file (Reference!A8), a particular tab (Reference!$A$2) and a particular range in this tab Reference!B6. Finally, it filters the data imported (only the 6th Col of the range, and only if on Col 10 the row has a particular value (Advanced Settings'!B5).

Is there a way to Import the name of the student as many times as countries they live in inside the same Array formula?

Right now, I am just adding more importrange (if there are 3 countries, I will add Importrange three times) with filters, but I would like to make it dynamic for the number of countries, without manual input every time. Also, the number of students imported varies every time so I can't look manually at the number of rows and then add a formula after the last cell of the array formula.

Thanks! EDIT: Sample Data and expected result:

1. Sample Data

2. Filter on both Class Level (4. Senior) and Country

The expected result here is that Alexandra appears twice as she's Senior and both US and UK.

CodePudding user response：

if Reference!B6 is a range and IMPORTRANGE for each country is the same try:

=QUERY({IMPORTRANGE(Reference!A8, Reference!A2&Reference!B6)},
 "select Col6 
  where Col10 matches '"&TEXTJOIN("|", 1, 'Advanced Settings'!B5:B)&"'", )

CodePudding user response：

IF C = Senior and E is splittable, create a array of corresponding As. Then, FLATTEN the array and REDUCE to remove all empty items in the array.

Sample:

=ARRAYFORMULA(
  REDUCE(
    "Senior List",
    FLATTEN(
      IF(
        C2:C6="4. Senior",
        IF(ISTEXT(SPLIT(E2:E6,",")),A2:A6,),
      )
    ),
    LAMBDA(a,c,IF(c="",a,{a;c}))
  )
)