Google-sheets – Function to copy fields from one sheet to another based on IF statements

google sheetsgoogle-apps-script

I'm trying to figure out a way to copy specific fields to other sheets based on IF statements.

I have 4 sheets, the Customer Subscription Sheet (Sheet 1), Month 1 (Sheet 2), Month 2 (Sheet 3) and Month 3 (Sheet 4).
I have customer subscriptions in sheet 1 containing a column that states how long a customer has subscribed for.

enter image description here

What I want to do is copy fields from sheet 1 to the other sheets based on how long the customer has subscribed for.

So if the customer has subscribed for 1 month, what I want to do, is copy that entire row to Month 1 (sheet 2).
If the customer has subscribed for 2 months then I want to copy the row to both Month 1 (sheet 2) and Month 2 (sheet 3).
Similarly, for 3 months, the row will be copied to all the sheets.

Link to sheet: https://docs.google.com/spreadsheets/d/1HPStHwqGOrTTOa07azEg_tQ3OfA0Uq-9ITX3ACaLyqk/edit?usp=sharing

I'd really appreciate it if someone could help me out with this. Any input is welcome 🙂

Best Answer

You can use the same QUERY formula (with minimum alterations) to get your results for all 3 tabs.

The formula to be placed in A1 of your Month 3 tab would be:

=query(Subscriptions!A1:K, " select * where D matches '(1|2|3).+' ",1)

Making a minor change in the query -change '(1|2|3).+' to '(1|2).+' or '(1).+'- you can use use the same formula for tabs Month 2 and Month 1 accordingly.

matches in a query allows us to use the regex language. In our formula the regex reads:
Find any of the grouped characters 1 or 2 or 3 followed by any character as many times as it may appear.

You should also read more about QUERY as well as how to use RE2 expressions.

Related Topic