0

I have this table in Google sheet:

Col A (Month) Col B (Date)
January 1/1/2023
January 1/2/2023
January 1/3/2023

And I have another table:

Col C (Dept.) Col D (KPI)
Dep1 KPI1
Dep1 KPI2
Dep2 KPI3

I want to repeat the first table for each item from the second table to be like:

Col E (Month) Col F (Date) Col G (Dept.) Col H (KPI)
January 1/1/2023 Dep1 KPI1
January 1/2/2023 Dep1 KPI1
January 1/3/2023 Dep1 KPI1
January 1/1/2023 Dep1 KPI2
January 1/2/2023 Dep1 KPI2
January 1/3/2023 Dep1 KPI2
January 1/1/2023 Dep3 KPI3
January 1/2/2023 Dep3 KPI3
January 1/3/2023 Dep3 KPI3

I tried to work with query inside an array formula but it is not working.

Please Help,

Thank you,

1 Answer 1

1

Use reduce(), like this:

=query( 
  lambda( 
    table1, table2, 
    reduce( 
      A1:D1, sequence(rows(table1)), 
      lambda( 
        result, rowIndex, 
        { 
          result; 
          byrow( 
            table2, 
            lambda( 
              table2row, 
              { 
                index(table1, rowIndex), 
                table2row 
              }
            ) 
          ) 
        } 
      ) 
    ) 
  )( 
    filter(A2:B, len(A2:A)), filter(C2:D, len(C2:C)) 
  ), 
  "order by Col4 format Col2 'M/d/yyyy' ", 1
)
1
  • This is amazing, Thanks
    – Sarah
    Commented Dec 27, 2022 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.