Search code examples
regexgoogle-sheetsgoogle-sheets-formulagoogle-query-language

Filtering Data from different sheets located in different columns matching two Conditions

Objective I'm trying to retrieve data from different sheets in my spreadsheet based on two conditions: Year = 2023 and Month = May. The goal is to have one formula that filters the data and displays the results in a consistent order (LINK, COMPANY, TITLE) when both conditions are met.

Problem I've constructed a formula that works correctly for one of the data sets (Monoti), but it's not functioning as expected for the other two (Rumala and Arbito).

  • For Monoti, it correctly returns three results.
  • For Arbito it returns one result even though it should return none (no rows meet the two conditions)
  • For Rumala, it returns one result but it isn't correct (as shown in the picture below, the data should be the second row but it comes from the first one).

Rumala yields the wrong result

Goal I'm looking for guidance on how to modify the formula to correctly filter data from different sheets based on the specified conditions. I want to retrieve the relevant data and ensure it is displayed in a consistent order (LINK, COMPANY, TITLE) for each row where the conditions are satisfied. Any suggestions or alternative approaches would be greatly appreciated.

Data Structure Each sheet has columns representing LINK, COMPANY, TITLE, Year, and Month, among others. The order of the columns may differ between sheets, but the required data (LINK, COMPANY, TITLE) is present in all sheets. Each column should have its own header but there should be only one table containing all data.

My attempt:

https://docs.google.com/spreadsheets/d/185CaPyHmg_dScbTCateG2HlmsBd85WDJnjx-_u49hnA/edit?usp=sharing

The formula I have used is

=QUERY(Rumala!A2:N, "SELECT N, J, D WHERE L='2020' AND M='May'", 1)

but it yields the wrong results. I have also tried using =SORT(FILTER) and REGEXMATCH but without any success.

=SORT(FILTER(Rumala!A2:N, (REGEXMATCH(Rumala!L2:L, "^2023$")) * (REGEXMATCH(Rumala!M2:M, "^May$"))), 1)

Expected result

  • Column A (Company) - retrieves columns I, M, N from sheets Monoti, Arbito, Rumala respectively.
  • Column B (Post) - retrieves columns B, I, J from sheets Monoti, Arbito, Rumala respectively.
  • Column C (Link) - retrieves column A from all sheets.

enter image description here

Column

Solution

  • Here's one approach you may test out:

    =let(Σ,{"Company","Title","Title_URL","Year","Month"},
     Λ,reduce(Σ,{"Monoti","Arbito","Rumala"},lambda(a,c,{a;choosecols(indirect("'"&c&"'!A2:Z"),index(xmatch(Σ,indirect("'"&c&"'!A1:Z1"),,-1)))})),
     {"Company","Post","Link";filter(choosecols(Λ,1,2,3),choosecols(Λ,1)<>"",choosecols(Λ,4)=2023,choosecols(Λ,5)="May")})

    enter image description here