Skip to main content
added 274 characters in body
Source Link
pjmg
  • 2k
  • 4
  • 11
  • 19

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.

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)

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.

added 285 characters in body
Source Link
pjmg
  • 2k
  • 4
  • 11
  • 19

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)

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

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)

Source Link
pjmg
  • 2k
  • 4
  • 11
  • 19

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