I would need to unify the data from columns Metod_1,Metod_2,Metod_3,Metod_4 and Metod_5 into a single column, but keeping the resolution date data associated to each row:


Having resolved the above issue, in my Data Studio report I would need to get the above information to display in this way, filtered by "Resolved" date:

chart example



Put this formula in the first row of a free column:

=arrayformula( query( 
    flatten( iferror( 
      vlookup( BD!A1:A; BD!A1:A; sign(sequence(1; columns(BD!D1:H))); false ) 
    ) ) 
  "where Col1 is not null 
   and Col2 is not null 
   order by Col1"; 
) )

Your sample spreadsheet's locale is Argentina, so the formula has to use ; as argument separator and \ as array expression horizontal separator.

You were using =IFERROR(SPLIT(C2;";";TRUE;FALSE);" ") to split values into columns. That introduces superfluous space characters in "empty" cells. Use this instead:

=arrayformula( if( len(C2:C); split(C2:C; ";"; false; true); iferror(1/0) ) )

  • thank you very much for your help! I applied the formula you suggested, but it gives the following error: #VALUE: Unable to parse the query string for The QUERY function parameter 2: NO_COLUMN: Col2. (Apologies, my level of Google Sheets is not very high). To help the understanding to be able to reproduce the error, I created this spreadsheet: docs.google.com/spreadsheets/d/…
    – Ger D
    Jan 10 at 14:18
  • Edited the answer. See your sample spreadsheet. Jan 10 at 14:32
  • you are a genius! thanks for the help, it worked very well!
    – Ger D
    Jan 10 at 14:38
  • Testing the solution you created, I had a question about how I can add more data to the formula. For example; from the "BD V2" tab I would need to add the columns to the "Solution" tab: - Labels - Líder delivery I would like to learn what part of the formulas I should modify to be able to scale the solution to N columns. Thank you very much!
    – Ger D
    Jan 12 at 15:58
  • Try using { array expressions }. You should ask only one question per post, so please post your new question in a new topic. Jan 12 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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