Repeat Rows Based on Column

[almo136]

I would like to create a spreadsheet that repeats the values found in one column numerous times an extra column based on the contents of a third column.

It is quite hard to explain so I have attached a sheet showing example input and desired output (in the second tab).

One the input column the number of rows will vary for SKU, Special Price and Website ID.

Thanks!

[JohnTopley]

Try

In Output

A2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,MOD(ROWS($A$1:$A1)-1,COUNTA(Input!$A:$A)-1),,1),"")

B2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,MOD(ROWS($A$1:$A1)-1,COUNTA(Input!$B:$B)-1),1,1),"")

C2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,INT((ROWS($A$1:$A1)-1)/(COUNTA(Input!$A:$A)-1)),4,1),"")

[José Augusto]

Try this in Output!A2 and copy to A2:B12

Please Login or Register  to view this content.

In Output!C2 and copy down the following formula

Please Login or Register  to view this content.

where Input!$C$1 is the number of sku items for website

See the file

[almo136]

Try

In Output

A2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,MOD(ROWS($A$1:$A1)-1,COUNTA(Input!$A:$A)-1),,1),"")

B2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,MOD(ROWS($A$1:$A1)-1,COUNTA(Input!$B:$B)-1),1,1),"")

C2

=IF(ROWS($1:1)<=(COUNTA(Input!$A:$A)-1)*(COUNTA(Input!$E:$E)-1),OFFSET(Input!$A$2,INT((ROWS($A$1:$A1)-1)/(COUNTA(Input!$A:$A)-1)),4,1),"")

Thanks. Worked perfectly.