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.

  • Maybe try this kind of solution : stackoverflow.com/questions/35037640/… – pjmg Aug 23 '19 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 Aug 23 '19 at 15:19
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 | |
  • I do not quite understand the answer and "copy paste" does not work. Can you break it down for me? – Tom Aug 23 '19 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 Aug 23 '19 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 Aug 23 '19 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 Aug 23 '19 at 16:04
  • Ok, so the columns with expanding array formulas get out of sync with the description not matching the entry. – Tom Aug 24 '19 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, privacy policy and cookie policy

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