Google Sheets – Remove Count Column by Repeating Rows

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-arrays

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.

Best Answer

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.

Related Topic