1

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows:

  Count | Entry |   Description   
 -------|-------|----------------- 
      2 | Foo   | yada yada       
      3 | Bar   | blah blah blah

On another worksheet I want to repeat each (not the header) in 'wi' by the Count such that:

  Entry |   Description   
 -------|----------------- 
  Foo   | yada yada       
  Foo   | yada yada       
  Bar   | blah blah blah  
  Bar   | blah blah blah  
  Bar   | blah blah blah

A custom formula is better than a gnarly formula composition but a solution of existing built-ins that is clean and manageable is preferred over code.

Improve this question
2
  • Maybe try this kind of solution : stackoverflow.com/questions/35037640/…
    – pjmg
    Commented Aug 23, 2019 at 8:28
  • Thanks for the pointer, i had gotten to =ARRAYFORMULA(SPLIT(REPT(wi!B2:B&"==",wi!A2:A), "==")) but if I try to transpose the split, I get "result was not automatically expanded, please insert more columns)
    – Tom
    Commented Aug 23, 2019 at 15:19

2 Answers 2

Reset to default
0

With arrays you can do it : 

  Count | Entry |   Description   
 -------|-------|----------------- 
      2 | Foo   | yada yada       
      3 | Bar   | blah blah blah

Using this with "Count" in A1; "Description" in C1; etc..

={B1:C1; transpose(ArrayFormula(SPLIT(transpose(ArrayFormula((REPT(B2:C2&",",A2)))),",",true,true)));transpose(ArrayFormula(SPLIT(transpose(ArrayFormula((REPT(B3:C3&",",A3)))),",",true,true)))}

Result : 

  Entry |   Description   
 -------|----------------- 
   Foo  | yada yada  
   Foo  | yada yada 
   Bar  | blah blah blah
   Bar  | blah blah blah
   Bar  | blah blah blah

Working example in A5

Edit : New solution using column 

    =transpose(split(TEXTJOIN(",",true,transpose(ArrayFormula(REPT(B2:B100&",",$A2:$A100)))),",",true,true))

Now to use it you juste have to expand it on every column. Don't forget to modify the range I used (B2:B100 and $A2:$A100)

Edit 2 : Added some check in case a cell from the original range is blank :

=transpose(split(TEXTJOIN(",",true,transpose(ArrayFormula(REPT(ArrayFormula(IF(B2:B4<>"",B2:B4,"BLANK CELL"))&",",$A2:$A4)))),",",true,true))

Don't forget to expand it for every column.

Improve this answer
7
  • I do not quite understand the answer and "copy paste" does not work. Can you break it down for me?
    – Tom
    Commented Aug 23, 2019 at 15:11
  • It can depends on your spreadsheet settings, the first issue that comes in my mind is if you do use , or ; in formulas. I'll break down my answer tomorrow but TL;DR: I used REPT to repeat the strings (with arrayformula because range is an array) then SPLIT to divide the string in separate cells (again with arrayformula 'cause the range is an array). TRANSPOSE is very useful because arrayformula only works in column, you can test it by yourself. Finally I used {} to put everything in an unique formula. Is the working example ok btw ?
    – pjmg
    Commented Aug 23, 2019 at 15:19
  • Thanks. It does work if I only use it on two source rows or add a sub-formula for each row - in my case about 100 but it fluctuates. How can this work for an arbitrary number of source rows?
    – Tom
    Commented Aug 23, 2019 at 15:42
  • Edit : I added a new solution where you just have to expand the formula with a drag on every column, you can check it on the updated sheet example. (Cells highlighted in yellow contain formula)
    – pjmg
    Commented Aug 23, 2019 at 16:04
  • Ok, so the columns with expanding array formulas get out of sync with the description not matching the entry.
    – Tom
    Commented Aug 24, 2019 at 6:24
1 

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(
 REPT({wi!B2:B&"♦"&wi!C2:C}&"♥", wi!A2:A)), "♥")), "♦"))

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.