Reapet n times each rows from query in google sheets

Question

I try to have an output by query - reapeting each rows by formula in google sheets after match the term (term is text from cell - after choose from drop down list)

I'd like to Select a,b,c,f,g,h etc) and reapeting each column 7 times. I have two formulas. Now by Query formula on Output i have each row only one time. And ArrayFormula to repeating - but it works only for 1 column

I'd like to combine 2 formulas below to choose all column and repeat each row 7 times.

=QUERY(MY_DICTIONARY!A5:F100; "SELECT a,b,c,f,g,h, WHERE A='"&F3&"'")

=SORT(TRIM(TRANSPOSE(SPLIT(QUERY(ARRAYFORMULA(
 REPT(A8:A50&"♠"; 7)); ;999^99); "♠"))); 1; 0)

I will be extremely happy to any help.
maybe It will possible to combine 2 formulas into 1?

Edit 09:08 I add example file to show my problem.

example google sheet

Welcome to Stack Overflow. Try reading the guide on asking questions and updating your post to include an example of your spreadsheet so others can better understand your question. The English in your post is very hard to understand; it might be a good idea to consult google translate or have a friend translate/edit your post for you, so that others can understand your problem and help you fix it. — sinaraheneba
Hello sinaraheneba. You have right. I will try to explain better my cases. I add example file on google to show my problem. — Gregory_Ame

Gregory_Ame Gregory_Ame · Accepted Answer · 2019-08-12T10:37:28

I'm trying to combine Array formula for all columns. But I discovered that it works well enough as a single Array of range each (D:D), (E:E), (F:F), (G:G).

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
 REPT(D4:D100&"♠"; 7); ;99^99); "♠"))))

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
 REPT(E4:E100&"♠"; 7); ;99^99); "♠"))))

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
 REPT(F4:F100&"♠"; 7); ;99^99); "♠"))))

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
 REPT(G4:G100&"♠"; 7); ;99^99); "♠"))))

Reapet n times each rows from query in google sheets

1 Answers