0

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 :)

  • Welcome. Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Jan 21 at 16:06
0

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.

| improve this answer | |

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.