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!!

 

[Tr15to]

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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.

Is there any way this could be altered to suit a decimal number of times repeated? Say 0.1 or 0.5?

 

[hiker95]

Is there any way this could be altered to suit a decimal number of times repeated? Say 0.1 or 0.5?

Tr15to,

Welcome to the MrExcel forum.

1. Can we see what your raw data worksheet looks like?

2. And, can we see what the results (manually formatted by you) should look like?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Tr15to]

I have attached the link here.

https://www.dropbox.com/s/9kr9uxsb0jf4pmb/Subdivision Template.xlsm?dl=0

The result will be split up into a dollar value based on the length of a project under time in column be. When I have a project duration under 10 Months as shown in the Total Construction period in cell F14 an error pops up that says the subscript is out of range. I'm guessing this means that the macro does not work for figures under 1.

Thanks for replying.

 

[hiker95]

Tr15to,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


Then in our next reply also include your macro:

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Tr15to]

https://www.dropbox.com/s/aizhiwto18pj8vh/Subdivision Template.xlsx?dl=0

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("C2").Resize(UBound(c, 1), UBound(c, 2)) = c
Columns(3).AutoFit
End Sub

 

[hiker95]

Tr15to,

Thanks for the workbook, Subdivision Template, with three worksheets.

I do not understand what you are trying to do.

Maybe someone else on MrExcel will be able to help you.