0

I want to know if there is a way that I can combine any of these 3 formulas.

Sample Sheet

I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(transpose(split(query(filter(substitute(substitute(Data!C2:C, " ", "_"), char(10), " " ),(Data!B2:B = "paid") + (Data!B2:B = "not paid")),"", 9^9)," ")),"_", " "))

Then, this splits that col. into unit & name: =ArrayFormula(if(len(A3:A), trim(regexextract(A3:A, "^(\d+)\s(.*)$")),)) Also in col. F, I run =Value formula to convert D to values, because sum doesn't work on D.

And finally, this groups the data in E:D and sums them: =QUERY(E3:F856,"SELECT E, SUM(F) GROUP BY E")

Is there anyway I can combine any of these formulas to clean up the sheet and reduce the number of columns I use?

I've tried to embed the 2nd formula into query, in various amateur ways & these give #value error:

=QUERY('*formula2*',"SELECT E, SUM(F) GROUP BY E")

=QUERY(E3:F856,"SELECT E, '*formula2*' SUM(F) GROUP BY E")

And also, embed formula 1 into formula 2:

=ArrayFormula(if(len(*'formula1'*), trim(regexextract(A3:A, "^(\d+)\s(.*)$")),))

If there is a way to clean up then great, but if there isn't then I'll have to deal with it.

enter image description here

2
  • 1
    You may find that it is much easier to maintain those three separate formulas instead of one monster formula that does it all. Jun 19 at 16:09
  • Wow thank you for the formula~ But I do agree with you, that one is definitely long, and at this point of my knowledge I also think I find the 3 separate formulas more understandable. Thank you so much again. I didn't think it would end up to be a monster formula :D
    – Genny
    Jun 19 at 17:07

1 Answer 1

1

Use this monster:

=arrayformula( 
  query( 
    iferror( 
      value( 
        trim( 
          regexextract( 
            substitute( 
              transpose( 
                split( 
                  query( 
                    filter( 
                      substitute( substitute(Data!C2:C, " ", "_"), char(10), " " ), 
                      (Data!B2:B = "paid") + (Data!B2:B = "not paid")
                    ), 
                    "", 9^9 
                  ), 
                  " " 
                ) 
              ), 
              "_", " " 
            ), 
            "^(\d+)\s(.*)$" 
          )
        ) 
      ), 
      trim( 
        regexextract( 
          substitute( 
            transpose( 
              split( 
                query( 
                  filter( 
                    substitute( substitute(Data!C2:C, " ", "_"), char(10), " " ), 
                    (Data!B2:B = "paid") + (Data!B2:B = "not paid")
                  ), 
                  "", 9^9 
                ), 
                " " 
              ) 
            ), 
            "_", " " 
          ), 
          "^(\d+)\s(.*)$" 
        ) 
      )
    ), 
    "select Col2, sum(Col1) 
     group by Col2", 
     0 
  ) 
)

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.