Repeat a cell multiple times based on a variable

[nojaja]

Hello - I'm new to these forums and I have a problem that seems simple but is somehow evading me at the moment. I haven't been able to find a good answer yet with a search so I figured I'd ask y'all.

I have a table of items in Column A and the number of times I want it to be repeated

Item	Times Repeated
Apple	3
Orange	4
Banana	2
Grapes	2
Kiwi	3

<tbody>
</tbody>

I am looking for an formula that would provide a result that looks like this in Column C

Apple

Apple

Apple

Orange

Orange

Orange

Orange

Banana

Banana

Grapes

Grapes

Kiwi

Kiwi

Kiwi

<tbody>
</tbody>

Thanks in advance!!

 

[BobUmlas]

A little VBA comes to the rescue.
Write this function:
Function rpt(rg1 As Range, rg2 As Range, which As Integer) As String
Dim fruit()
Dim n As Integer, m As Integer
ReDim fruit(1 To Application.Sum(rg2))
For i = 1 To rg2.Rows.Count
For j = 1 To rg2(i)
n = n + 1
fruit = rg1(i)
Next
Next
If which <= UBound(fruit) Then
rpt = fruit(which)
Else
rpt = ""
End If
End Function

In cell C1, enter:
=rpt($A$2:$A$6,$B$2:$B$6,ROW(A1))
and fill down.

 

[hiker95]

nojaja,

I can give you a macro solution that will adjust for a varying number of rows.

Sample raw data:

Excel 2007
	A	B	C
1	Item	Times Repeated
2	Apple	3
3	Orange	4
4	Banana	2
5	Grapes	2
6	Kiwi	3
7
8
9
10
11
12
13
14
15
Sheet1

After the macro:

Excel 2007
	A	B	C
1	Item	Times Repeated	Apple
2	Apple	3	Apple
3	Orange	4	Apple
4	Banana	2	Orange
5	Grapes	2	Orange
6	Kiwi	3	Orange
7			Orange
8			Banana
9			Banana
10			Grapes
11			Grapes
12			Kiwi
13			Kiwi
14			Kiwi
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RepeatData()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735863-repeat-cell-multiple-times-based-variable.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long, n As Long
a = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
n = Application.Sum(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row))
ReDim c(1 To n, 1 To 1)
For i = LBound(a, 1) To UBound(a, 1)
  If a(i, 2) <> "" Or a(i, 2) <> 0 Then
    For n = 1 To a(i, 2)
      ii = ii + 1
      c(ii, 1) = a(i, 1)
    Next n
  End If
Next i
Columns(3).ClearContents
Range("C1").Resize(UBound(c, 1), UBound(c, 2)) = c
Columns(3).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RepeatData macro.

 

[nojaja]

Thanks you two! I will try to adapt your answers to my workbook and let you know how it goes. If there is a way to do this without VBA that would be fantastic

 

[XOR LX]

Or, formula-based solution: assuming the table you give is in A1:B6 (with headers in row 1), enter this array formula in C2 and copy down as required:

=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,MMULT((--(ROW($A$2:$A$6)>=TRANSPOSE(ROW($A$2:$A$6)))),$B$2:$B$6)>=ROWS($1:1),0)),"")

Regards

 

[Marcelo Branco]

A possible formula solution

	A​	B​	C​
1​	Item​	Times Repeated​	List​
2​	Apple​	3​	Apple​
3​	Orange​	4​	Apple​
4​	Banana​	2​	Apple​
5​	Grapes​	2​	Orange​
6​	Kiwi​	3​	Orange​
7​			Orange​
8​			Orange​
9​			Banana​
10​			Banana​
11​			Grapes​
12​			Grapes​
13​			Kiwi​
14​			Kiwi​
15​			Kiwi​
16​
17​

<TBODY>
</TBODY>

Array formula in C2
=IF(SUM($B$1:$B$6)>=ROWS(C$2:C2),INDEX($A$2:$A$6,MATCH(ROWS(C$2:C2)-1,SUBTOTAL(9,OFFSET($B$1:$B$6,,,ROW($B$1:$B$6))))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

copy down

M.

 

[hiker95]

Marcelo Branco,

Nicely done - one for my archives - thanks.

 

[Marcelo Branco]

Marcelo Branco,

Nicely done - one for my archives - thanks.

hiker,

Thank you very much for your kind words.

M.

 

[meytithveasna]

here is another:

IFERROR(INDEX($A$2:$A$6,MATCH(1,N(($B$2:$B$6>COUNTIF($C$1:C1,$A$2:$A$6))),0)),"")
confirm with control,shift,enter

 

[deciog]

nojaj


My contribution confers


=LOOKUP(ROWS($1:1),SUMIF(OFFSET(B$1,,,ROW($1:$6),),"<>")+1,A$2:A$6)&""


Decio