2

I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet.

Then I am aggregating those three sheets into one and also filter them using a query.

Ex:

=query({BUOne!A1:Y200;BUTwo!A1:Y200;BUThree!A1:Y200},
"Select * where Col1 is not null AND Col14 = 'Bench' order by Col2 ASC")

This gives me the data I want from all 3 sheets without a problem.

But I want to know from which sheet each data row came from. In this case to which BU(BU_One,BU_Two,BU_Three) each record belongs to.

Is there a way to put a tag or a different column when aggregating sheets so I can know this?

1
  • try like this:

    =ARRAYFORMULA(QUERY({
     BUOne!A1:Y200,   TRANSPOSE(SPLIT(REPT("×BU_One",   200), "×")); 
     BUTwo!A1:Y200,   TRANSPOSE(SPLIT(REPT("×BU_Two",   200), "×")); 
     BUThree!A1:Y200, TRANSPOSE(SPLIT(REPT("×BU_Three", 200), "×"))},
     "where Col1 is not null 
        and Col14 = 'Bench' 
      order by Col2"))

  • This works perfect except that the new column gets a header as BU_One. Which i can work with at the moment. Could you please explain how it does the trick? So i can apply the same working in different scenarios. :) – direndd Jun 26 at 11:34
  • try perhaps like this: =ARRAYFORMULA(QUERY({ BUOne!A1:Y200, {"From"; TRANSPOSE(SPLIT(REPT("×BU_One", 199), "×"))}; BUTwo!A1:Y200, TRANSPOSE(SPLIT(REPT("×BU_Two", 200), "×")); BUThree!A1:Y200, TRANSPOSE(SPLIT(REPT("×BU_Three", 200), "×"))}, "where Col1 is not null and Col14 = 'Bench' order by Col2")) – user0 Jun 26 at 18:54

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.